XL 2021 Liste dans liste

[Constantin]

Bonjour,
En PJ mon fichier "courses macro" qui contient 3 feuilles.
La première (Feuil1) contient ma liste de courses:
- colonne A = catégorie du produit (liste déroulante)
- colonne B = rayon où se trouve le produit affiche une liste déroulante lié à la catégorie.
Problème n°1 : Pour chaque choix dans la colonne A, afficher dans la colonne B la liste déroulante correspondant à la catégorie pour chacune des cellules A2 à A70.
- Colonne C et D : texte libre pour préciser l'article et sa quantité
- colonne E : permet de cocher le produit (x) pour l'ajouter au panier.
- colonne F : Précise l'emplacement du rayon sur le plan
Problème n°2 : Comment rattacher, pour chaque ligne, l'emplacement du rayon précisé dans le tableau de la feuille "Localisation" ?
La feuille "Localisation" précise la position du rayon concerné (dans mon exemple, seule la ligne "Technologie" est complétée.
La feuille "Plan" contient le plan du magasin (très incomplet pour l'instant...). Seul le rayon "Télévision" est associé à une référence géographique (A3) dont l'apparence change selon que le produit de la liste est sélectionné (x dans la colonne E) dans la cellule correspondant au produit (mise en forme conditionnelle).
Problème n°3 : Mettre le plan à jour en fonction du CHOIX(x)
Bien sûr, les propositions que j'ai formulées ne sont présentées qu'à titre d'exemple en Feuil1 (tableau J33:M50)

Pourquoi cet outil ? Étant malheureusement une personne dite à mobilité réduite, lorsque je fais mes courses, j'ai à cœur d'optimiser mon cheminement dans le magasin en fonction des points de collecte. J'arrive encore à faire mes courses, le charriot de courses faisant office de déambulateur à roulettes.

Merci d'avance de votre savoir et de votre aide
Pierre

 

[vgendron]

Hello

j'ai commencé à regarder:
je pense que tu aurais tout intérêt à utiliser des tables structurées pour lister les "types" et les produits associés
==>regarde la feuille "Listes"
chaque table a un nom qui commence par "t_"
les noms ne peuvent pas contenir d'espace (que j'ai remplacés par des "_") et pas de caractères spéciaux tels que le point (je les ais supprimés)

dans la table "t_ListeCourses", on utilise des formules à base de indirect et nom des tables pour alimenter les validations de données

dans les tables de la feuille Listes, chaque table a sa colonne "Loc"
==> on peut donc utiliser une formule pour aller chercher la loc en fonction du produit de la colonne B de la liste des courses

ensuite.. pour le pb 3: je pense que vas pouvoir donner plus de précisions lorsque ton projet sera plus abouti (notamment avec le plan du magasin..?)

 

[Constantin]

Merci infiniment,
Ça se rapproche énormément de ce que j'attends et il va me falloir un peu de temps pour digérer et comprendre ces formules. Mais c'est déjà magnifique !
J'ai quelques petits points que je ne comprends pas (c'est normal vu mon niveau...):
- Feuil1 , quand j'active la liste déroulante de la colonne Rayon, la liste affiche les cellules du champ localisation. ça me faisait la même chose avec RECHERCHV si je sélectionnais comme plage le tableau de localisation. Peut-on épurer cette liste pour exclure des listes déroulantes des cellules Rayon les valeurs de localisation? (A1, A2,...). Je dis ça comme ça, mais dans liste, peut-on supprimer les colonnes "B,E,H,K,N,Q,T,W etc" pour éviter ces informations dans les listes déroulantes de Rayon ?
- A l'ouverture du fichier, la ligne 3 affiche comme localisation B5... Pourquoi cette valeur qui n'est renseignée nulle part?
Par contre, ça matche bien entre la localisation "CATEGORIE/RAYON" et la ligne Technologie de localisation.
Concernant le plan de disposition des gondoles, j'ai sollicité Leclerc pour que l'on m'envoie quelque chose que je pourrais adapter au plan.
Je pense à quelque chose : faire deux tableaux, un sans les colonnes Localisation (voir tableau feuil1 de mon fichier) et celui que tu as construit dans Liste pour incrémenter la Localisation, le premier servant uniquement à l'affichage de la liste déroulante des cellules Rayon ? Mais là, la syntaxe me dépasse.
Comme tu le vois, je demande beaucoup et je ne sais pas grand chose.
En tous cas, c'est la première réponse que je reçois et elle est très prometteuse.
Mille mercis,
Pierre

 

[vgendron]

j'ai rectifié la formule pour les données validations pour n'avoir QUE la colonne des produits (les localisations ne sont plus listées)

pour les colonnes B E .... je les avais juste mises pour séparer les différentes tables==> elles ne servent à rien, tu peux les supprimer

j'ai pensé à une solution avec juste UNE table à 3 colonnes
1ere colonne= le type
2eme colonne= le produit
3eme colonne = localisation

il faut donc que je trouve une formule qui permet de lister uniquement les produits qui correspondent au type selectionné.. si je trouve, je reviens vers toi

quelques clés pour comprendre les formules pour les validations de données
on ne peut pas mettre directement le nom de la table, il faut passer par un indirect(nomtable)
nomtable est "fabriqué" à partir du préfixe "t_" et contenu de la colonne A

si la table est sur UNE colonne (comme c'est le cas pour t_type) indirect(Nomtab) est suffisant
mais si la table est sur plusieurs colonnes
en restant avec indirect(NomTab) ==> toutes les colonnes sont listées ==> c'est qui te posait problème à l'instant avec la localisation
il faut donc en plus préciser la colonne
on fait ca avec la fonction "Index"

 

[Constantin]

Bonjour "vgendron",
Ça marche super bien ! Inutile de te dire que j'ai tout à apprendre sur les fonction "INDEX" et "INDIRECT" et sur leur syntaxe...
Pendant que tu te creusais les méninges pour me trouver cette solution parfaite, moi, trouvant les programmes TV nuls, je me suis relevé pour explorer les validations des données, chose à laquelle je n'avais pas pensé plus tôt. Je me suis dit qu'en ajoutant une copie du tableau de listes expurgées des Loc et colonnes vides, j'arriverai peut-être à quelque chose. Que nenni ! Rien ne marche et pourtant, j'ai fait des efforts et des tonnes d'essais.
Dans la feuille1 j'ai modifié deux lignes (en blanc) et ça bloque sans que je comprenne pourquoi. Juste pour ne pas mourir idiot, j'aimerai comprendre ce que j'ai mal fait. Ça marche pour la catégorie mais ça coince pour rayon.
Tu pourras te rendre compte de l'étendue de mon ignorance avec le fichier joint.
Il me reste à attendre que sa seigneurie Leclerc daigne m'envoyer un plan que je puisse adapter à mon outil.
Sinon, il vont me voir pousser mon charriot dans tous les rayons pour établir moi-même ce plan (la Sécurité m'a fait comprendre qu'ils n'allaient pas aimer )
Je te tiens au courant. Bonne journée !
Pierre

 

[vgendron]

Hello
nouvelle version avec une seule table "t_Listes"
j'ai essayé d'expliquer les éléments des formules utilisées pour les données de validation

 

[Constantin]

De mieux en mieux,
Inutile, là encore, d'avouer que je n'aurai jamais trouvé cela tout seul. Tes explications m'ont été fort utiles et je t'en remercie.
En fouillant dans les Macros, à part les miennes ultrabasiques mais qui ne fonctionnent plus (je vais les refaire), il y en avait une que je ne connaissais pas (CréerTab). Dis-moi si je me trompe... J'ai supposé que cette macro s'était créée lors de la mise en forme du tableau dans Listes et qu'elle ne devait pas plus avoir de rôle après la création de celui-ci. J'ai fait un test en mettant toutes les lignes du code en commentaires ( ' ). A première vue, le tableau étant créé, cette macro n'intervient plus. Ce que je ne comprends pas, c'est la mise en forme du tableau avec l'alternance de couleurs, un peu comme si on utilisait la mise en forme conditionnelle avec une formule comme Mode... Je peux cependant changer de couleur les cellules mais si je choisis "aucune couleur", il affiche l'alternance de base...
Pour autant, tout fonctionne très bien:
- j'ai pu coloriser les cellules du tableau pour mieux les visualiser
- j'ai pu, simplement en tirant, ajouter deux catégories de produits et leurs rayons correspondant. Là encore, c'est magique !
Il ne me reste plus qu'un petit problème à résoudre : l'affichage sur le plan des points de collecte liés aux choix de ma feuille1 :
Supposons que je décide d'acheter deux téléviseurs dans Feuil1, la cellule de collecte (A3) va apparaître deux fois. Pour l'heure, la cellule A3 ne s'affiche dans le plan que si la cellule de choix (E2) contient un " x ".
Comment faire pour que cet affichage prenne en compte l'ensemble de la colonne F ? Si tu trouves une solution, je l'appliquerai pour chacun des points de collecte.
En fait, pour activer la mise en forme des points de collecte sur le plan, je pense qu'il faut une double condition.
Dans le cas du point D1, il faut que je recherche en même temps les " x " de la colonne CHOIX(x) et les valeurs "D1" de la colonne localisation et que j'associe ces deux conditions dans la mise en forme conditionnelle du point de collecte D1 sur le plan (CHOIX(x) = x & Localisation = D1). Le problème, c'est que je ne sais pas trop comment m'y prendre (comme d'habitude).
Après ça, il y en aura bien de fait et il ne me restera plus qu'à faire le plan et positionner tous mes points de collecte. Ça va être délicat pour faire entrer toutes les travées et autres gondoles dans le plan... C'est grand un Leclerc !
A bientôt
Pierre

 

[vgendron]

Rapidement,
la macro CréérTab : je l'ai faite pour generer les différentes tables structurées (lorsque les données étaient sous forme de plusieurs tableaux:==C'était plus rapide pour créer toutes les tables avec leur nom en "t_..."

maintenant que toutes les données sont dans une seule table. ca n'a plus lieu d'etre: tu peux la garder pour le fun

l'alternance de couleur dans les lignes: ca vient du style de la table
tu peux changer/ choisir parmi toutes celles qui existent
1) clic dans la table
2) ruban "conception de la table"
3) Styles de tableau

pour le reste, je regarderai un peu plus tard, j'ai un montage video à terminer

 

[Constantin]

Bonsoir cher XLNaute,
Pendant que tu travaillais sur ta vidéo (j'espère que tu es arrivé à tes fins) , moi, je faisais de la compote de rhubarbe (7 kg de tiges épluchées). Ma femme aime ça, moi, ça me tord le bec (trop acide).
Entre les coups, je me suis dis que je pouvais peut-être me creuser ma cervelle de retraité... J'ai fais quelques modifications (soyons fous !) :
- Dans Feuil1, j'ai chainé l'affichage de la la localisation à celui d'un "X" dans CHOIX(x). Pas de " x " pas d'affichage de la localisation
=SI([@[CHOIX (x)]]="";"";SIERREUR(INDEX(INDIRECT("t_listes[Loc]");EQUIV([@RAYON];INDIRECT("t_listes[Item]");0));""))
Cela me permet d'effectuer des recherches sur la seule colonne "Localisation" et d'éviter toutes sortes de formules que je ne maîtrise pas du tout (ou si peu).
Je vais évidemment rallonger ma liste course (100 produits) et associer les localisations lorsqu'elles sont sélectionnées dans la colonne CHOIX (x)
Du coup, dans mon Plan, je peux attribuer une mise en forme conditionnelle pour chacun des points de collecte.
Je sais, c'est l'âge de pierre, mais faute de mieux c'est ce que j'ai trouvé de plus simple.
J'ai fais mes courses il y a trois jours... J'avais 60 articles à prélever et c'était des petites courses rapides.
Toutes mes courses avaient été consignées sur un calepin et j'ai passé mon temps à passer de gauche à droite et de haut en bas dans le magasin parce que la liste couvrait deux pages... Qu'est ce que ce sera à Noël ?
Je me doute bien qu'à chaque point de collecte dans le plan, il me faudra affecter une valeur propre à sa valeur... Soit à cet instant 81 points de collecte à renseigner un par un avec la mise en forme conditionnelle dans le plan, sans compter ceux que je vais ajouter (tant en CATEGORIE qu'en RAYON) avant de renommer chacun d'eux dans un ordre logique de cheminement lorsque le Plan sera finalisé (si c'est raisonnablement envisageable ?)
Bon, j'ai montré mon petit outil primitif à mon épouse et elle a trouvé ça intéressant pour un vieux comme moi soucieux du nombre de pas qu'il fait. (Quand ma femme fait les courses avec notre calepin, il lui faut 1 heure de plus que moi et elle oublie toujours deux ou trois produits - C'est pour ça que c'est moi qui les fais.
Je te livre ma dernière mouture pour que tu me dises si c'est cohérent et surtout améliorable, ce dont je ne doute pas.
Bien sûr, l'outil que je te présente est loin d'être finalisé. Dans Feuil1, colonne I, je me suis arrêté à "Nbre de A5".
Bien cordialement,
Pierre

 

[vgendron]

Hello

Nouvelle version ci-jointe
j'ai corrigé les macro de tri filtre et autres
j'ai corrigé les formules dans la table: je me suis rendu compte que plusieurs catégories pouvaient avoir le meme item (ex Accessoires)

j'ai ajouté une macro "maj des tables" qui permet de colorer la table "t_listes" en fonction des couleurs de la table "t_type"

et une macro "plan" qui permet de colorer toutes les localisations
Dans la feuille "Plan", j'ai mis en gris une plage nommée "Plan_Magasin"

 

[Constantin]

Bonjour,
Tu penses bien que si je ne t'ai pas répondu plus tôt, c'est que j'essayais de comprendre et tester cette version.
Eh ben (comme on dit en Mayenne), je rame...
Si j'ai bien compris (c'est loin d'être sûr), quand je sélectionne dans feuil1 une Catégorie assortie d'un Rayon, la colonne Localisation affiche le point de collecte pour peu :
- que Catégorie et Rayons soient renseignés (sinon #VALEUR)- Comment remplacer ce terme par autre chose ?
- que la liste ait été complétée (sinon = 0)
J'ai aussi compris que le bouton Plan exécutait une macro de mise à jour des données à prendre en compte dans la recherche des points de collecte du Plan avant d'afficher le Plan.
Malheureusement, ça ne marche pas. Les formats des cellules (bien sûr renseignées dans le Plan) ne varient pas lorsque j'active ou non les X de la colonne CHOIX(x) de Feuil1, ce que je souhaite pour baliser visuellement mon cheminement dans le magasin.
C'est à cet effet que j'avais créé des recherches de valeurs dans Feuil1 et associé l'affichage de la localisation à la condition que x soit saisi dans CHOIX(x). Évidemment, il m'aurait fallu autant de lignes de "nbre" (colonnes I & J qui ne servent plus à grand chose dans cette nouvelle version) pour chacun des items de Listes et autant de mises en forme conditionnelle pour chacun des points de collecte dans le Plan...
Je sais, je suis chiant... Je me rends compte que l'exportation des données de recherche vers le plan pose de nombreux problèmes :
- réalisation du plan sur la feuille "Plan" ce qui suppose que j'ai à ma disposition les données de Leclerc
- ce plan sera t-il lisible sur une seule page ?
- complexité des formules et très nombreuses macros dont je ne maitrise pas du tout la logique d'exécution (c'est normal)
Je conçois sans peine que mes souhaits sont difficiles à exhausser.
Tu m'as déjà beaucoup appris, et je n'ai pas fini de décortiquer tes formules magiques.
Pour autant, si tout ce qui a attrait à la Feuil1 fonctionne bien, le plan n'affiche pas les valeurs souhaitées. Il est vraisemblable que je ne sais pas trop comment faire... J'ai créé de nouveaux points de collecte dument renseignés dans Listes et s'ils s'affichent correctement dans Feuil1, dans le plan, bien que renseignés, leur mise en forme relative à la macro liée à Plan ne semble pas fonctionner correctement.
En bref, tu fais comme tu le sens, moi je vais essayer de m'adapter avec ce que tu m'as déjà envoyé.
Ne te casse pas trop la tête avec la mise en forme des cellules dans les macros. Ça prend de la place dans le code et ne rend pas plus lisible le plan.

 

[vgendron]

Hello

Je suis aussi originaire de la Mayenne - Laval

effectivement, la coloration en rouge des points de collecte dans le plan ne tient pas compte de la case "Choix(x)"
mais en fait, ce n'est pas la macro en cause, juste la formule de la colonne "Nb" de la table 't_PointsCollecte"

il suffit de modifier la formule pour ne compter QUE si x est coché
la macro colore uniquement les points de collecte dont le nombre est <>0
en PJ, j'ai donc modifié la formule, et j'en ai profité pour supprimer la ligne avec le critère "x" qui ne sert à rien en fait

donc, effectivement, dans la feuille "Listes", il faut que les 2 tables soient complètes sous peine d'avoir des 0

Dans le plan, la mise en forme se contente d'écrire en rouge le point de collecte, mais on peut aussi colorer la cellule pour que ce soit plus visible.. la. c'était juste pour l'exemple.
dans la PJ j'ai ajouté la coloration de la cellule en vert

 

[Constantin]

Tu es courageux, cher Mayennais,
Malheureusement, ça ne marche toujours pas complètement...
Quand j'active un x dans la colonne CHOIX(x) pour un item présent sur le plan (A5, A2, D1 à D4 et A3), le texte sur le Plan passe bien en rouge et les fonts de cellules en vert.
Par contre, quand j'efface tous les "x", la couleur du texte repasse bien en Noir mais la fond reste vert au lieu de repasser en gris. Je dis ça comme ça, mais cela ne viendrait pas de l'instruction TintAndShade ? J'ai essayé la valeur 1 ou de mettre en commentaire cette instruction. Rien n'y fait. J'ai essayé de remplacer cette valeur par Interior.color=RGB(255,128,128) - comme ça au pif, VBA n'a pas aimé du tout !
Je ne suis pas doué, c'est tout.
Peut-être manque t-il une instruction pour revenir au format initial (Noir sur fond Gris) dans le cas où le ou les "x" sont effacés ?
Si je comprends bien aussi, il faudra que je complète t-PointsCollecte, ça sera long mais au moins je sais faire !
Mille mercis pour ta patience,
Bien cordialement,
Pierre
PS:Je ne suis Mayennais d'adoption que depuis 30 ans et des poussières et Angevin de naissance

 

[vgendron]

Hello

il manquait juste la partie pour effacer la coloration des cellules

 

[Constantin]

Hello !
Alors là, je prends mon pied !
Tout fonctionne à merveille. Je me suis permis d'apporter quelques modifications à ton code pour faire plus joli...
- taille police par défaut du Plan= 9
- Bold = false
- Fond du tableau bleu
- Afficher les choix sélectionnés en Taille 11, Gras, Rouge et Fond jaune
Tu t'en doutes encore une fois, il m'a fallu bien comprendre tes étapes de programmation pour comprendre que selon les cas "Bold" n'était pas synonyme de Font.Bold (mais je suis content que ta proposition m'ait apportée tant de réponses et d'interrogations.
Il me reste un dernier petit problème que j'ai essayé de résoudre avant de t'embêter...
La mise en forme du Plan (T-PlanMagasin) recouvre bien sûr toute cette sélection et la réinitialise à chaque activation de la macro "IdentifierPointsCollecte" .
Mon problème, c'est que je voudrais exclure de cette mise en forme les noms et formats des travées et emplacements signalés sur le Plan en encadré. J'ai essayé de déprotéger les cellules du Plan à l'exception des cellules encadrées. Tu ne me croiras pas, VBA n'a pas du tout aimé
De même pour les texte du plan : je voudrais pouvoir modifier la couleur des items par défaut (violet par exemple) à l'exception des textes contenus dans les encadrés.
Si tu as une idée, je suis preneur
J'ai contacté Leclerc pour avoir des nouvelles, il m'ont répondu que le plan était disponible sur leur site. Mes fesses : Rien.
Je sens que je vais arpenter la boutique pour faire moi-même mes relevés (ils ne vont pas aimer et mon dos non plus...)
Bien amicalement