Liste cascade 3 niveaux, plusieurs critères, plusieurs résultats

[Résolu/Fermé]
Signaler
-
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
-
Bonjour,

J'ai une liste de matériaux classée par catégories et sous catégories. Je cherche à faire une liste déroulante des matériaux correspondants à 2 critères texte (catégorie et sous catégorie).
J'ai réussi à mettre en place mes 2 listes déroulantes de choix : liste 1 = les catégories ; liste 2 = les sous catégories correspondant à la catégorie choisie en liste 1.
Mais voila je n'arrive pas à mettre en place cette troisième liste en fonction des 2 premières!
Cela fait 3 jours que je cherche et que j'essaye toutes les solutions proposées sur le net (à base de Index, Decaler, RecherheV, TCD ...), mais je n'arrive pas à l'adapter à ma problématique! Auriez-vous une solution à me proposer svp?
Si vous avez d'autres idées que les listes en cascades... notamment quelque chose qui se ferait sur une seule cellule... je suis preneuse!

Pour info je suis sur Excel 2007...

Merci

12 réponses

Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
Bonjour
quelques réponses, car je ne comprend pas tout dans votre retour:

Rassembler par catégorie
oui, pour pouvoir alimenter le menu déroulant correspondant il faut que soient groupés les codes de la feuille PrixMTX identiques en A et B
voyez par exemple la ligne 31 du modèle que je vous ai retourné et qui devrait se retrouver en ligne 27

Automatiser le nommage de la,colonne C de PrixMTX
????? je ne vois pas ce que vous voulez, cette colonne compte des informations qui n'existent nulle part ailleurs. Comment faire autrement que de les entrer au clavier?
Pouvez vous complèter vos explications?.. et surtout me dire avec ça ce que vous voulez afficher en E de >SDPvierge avec ces infos

Problème référence unique décalée
Ça m'avait échappé;Le problème vient du champ nommé "Champ" (que j'ai utilisé dans la formule, qui commence en ligne 7) alors que les colonnes de recherche (TCD..) commencent en ligne 6. Revoyez le nom du champ en ligne 6

En complément

Si vous le voulez, on peut construire la feuille SDPvierge de façon à ce que les formules en E ne soient jamais détruites.
Le principe:


_placer la validation de l'info de E en colonne C au lieu de E
_formater la police de la colonne C de façon à ce que les valeurs affichées ne soient pas visibles
_modifier la formule en E pour qu'elle affiche automatiquement > soit la valeur trouvée si réf unique, >soit la valeur affichée en C
_ et pour être complet, placer un code VBA dans le feuille qui efface les valeurs de la colonne C lorsqu'on change une valeur en A ou B, de façon à ce que d'éventuelles valeurs précédentes soient effacée en cas de changement ces références
Si vous souhaitez mettre ça au point, mettez à jour le fichier en fonction des remarques ci dessus et retournez le moi

A vous lire

Crdlmnt

1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 42674 internautes nous ont dit merci ce mois-ci

Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
... j'ai compris votre bonne idée qui permettra de ne pas renommer les nouvelles entrées (ce sera fait avec les codes catégories)
je l'ai combinée avec ma proposition dans ce fichier

https://www.cjoint.com/c/EAtwngGKlE8

Ainsi quand vous entrer dans la feuille PrixMTX une nouvelle valeur en A ou B, une nouvelle liste va se créer automatiquement dans la feuille LISTE sous la rubrique nommée Catx_ suivante
De même si vous entrez une nouvelle valeur en C avec des valeurs A & B existantes, elle viendra s'ajouter à la liste correspondante déjà établie

crdlmnt
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 42674 internautes nous ont dit merci ce mois-ci

Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
Bonjour

Je ne suis pas très violent en VBA mais vous pouvez essayez ça

1° placer cette formule en A1 de votre feuille, elle renvoie le nom de l'onglet

=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)


commencez votre macro par cette ligne

Sheets([A1].Value).Activate
...
...
...

End Sub

En principe, la macro devrait s'adresser à l'onglet sélectionné (dont le nom est renvoyé en A1)

sous toutes réserves

crdlmnt
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 42674 internautes nous ont dit merci ce mois-ci

Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
Bonjour
votre fichier en retour
https://www.cjoint.com/c/EAxlkzYCcWr

pour info:

afficher VBA/ Module+ / ouvrir Module 1

la macro PRO est écrite dans le module 1
le mot pour déverrouiller est toto
pour verrouiller la feuille active: ctrl + shift + K

pour modifier le mot de passe:
afficher module 1 et modifier le texte entre guillemets

pour modifier l'affectation de la touche clavier
onglet développeur / Icone Macro / Sélectionner PRO dans la liste et cliquer sur Option

Il faut éventuellement protéger VBA pour éviter que la macro, et donc le mot de passe soient lisible:

afficher VBA > outil > Propriétés de VBA > protection.

Bien cordialement
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 42674 internautes nous ont dit merci ce mois-ci

Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
Vi...
.... vous avez bien fait, mais en fait, la macro verrouille la feuille, ne la déverrouille pas. Mais effectivement, il faut la reverrouiller si vous enlever la protection
l'essentiel est que ça fonctionne
Bonne route
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 42674 internautes nous ont dit merci ce mois-ci

Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
Bonjour
il y a sans doute des solutions, mais pour pouvoir les proposer, il faudrait disposer de la configuration de vos données de départ!
pouvez vous mettre un fichier (même bidonné si confidentialité) à disposition sur:
https://www.cjoint.com/
et revenir ici coller le lien donné par le site
crdlmnt

Errare humanum est, perseverare diabolicum
Voila un fichier d'exemple pour présenter ma structure
http://cjoint.com/?0AqozI8qkWs
Merci
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
vi!!!...mais ça manque un peu d'explications, non?
vous voulez quoi et où? sachant par exemple que dans la colonne E de PrixMTX, vous avez une cellule avec formule et une cellule avec validation>?
>
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021

oui c'est vrai! il faut que j'explique par rapport au fichier! excusez moi!
Donc dans l'onglet SDPvierge, j'ai mis en jaune la zone qui nous intéresse!
Donc colonne A j'ai ma liste 1 de catégorie.
Colonne B j'ai ma liste 2 de sous catégorie en fonction de la catégorie choisi en liste 1.
Colonne E c'est la que je veux une liste déroulante en fonction des critères sélectionné en A et B.
Et la base de donnée dans laquelle la recherche doit être faite est l'onglet "PrixMTX". Je veux une liste des matériaux se trouvant dans la colonne C et qui correspondent aux 2 critères en colonnes A et B.
J'ai également un onglet xData ou l'on retrouve les noms des catégories et sous catégories qui me servent dans mes listes.

Voila j'espère avoir été un peu plus clair!
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061 > Nadège
Bonjour
Je vous retourne une proposition, incomplète car d'une part il faut bien vous laisser un peu de boulot, et d'autres parts, il y a des infos non conformes dans votre modèle qui me gêne pour compléter ma réponse.

https://www.cjoint.com/c/EArmdi2vBmR

Le principe

En feuille SDPvierge

Dans la, colonne C , j'ai placé une formule qui regroupe les textes entrés en A et B. Cette colonne peut être masquée où se situer hors champ mais nécessairement dans la même feuille. Elle sert à contourner le fait que la validation ne peut se référencer que sur une seule colonne.

Dans la colonne E, j'ai placé à la fois une validation selon la référence crée en colonne C et une formule qui affiche

__Soit le résultat si A et B n'existe qu »une fois dans la base de données
__Soit affiche « à sélectionner » si elle se retrouve plusieurs fois

Bien entendu, cette formule disparaît lorsque vous utilisez la validation. J'ai donc placé cette formule aussi en >>E2>9 pour que vous puissiez au cas où, la copier coller sur la cellule effacée

Notez que cette formule est matricielle elle doit être entrée avec la touche ENTER en maintenant les touches shift et ctrl enfoncées. Elle s'affiche entre accolades dans la barre de formule

En feuille PrixMTX

J'ai négligé les champs que vous verrez en bleu car ce sont des références uniques gérées par la formule
J'ai ensuite commencé à nommer les champs groupés avec les noms composés sans blanc, par l'assemblage de A et B, donc le nom que l'on trouve en C de SDPvierge
Ainsi, la valeur de la colonne C de SDP vierge permettra d'afficher en E la liste correspondante

Ce qui reste à régler :
__Certaines références de la feuille PrixTX ne sont pas groupées, voir par exemple : Transport fournisseurs que j"ai passé en rouge pour info
__Reste des champs à nommer comme expliqué ci-dessus
__Reste aussi si besoin, à remplacer les blancs éventuels dans les textes en A et B de PrixMTX par des underscore, car sinon ils ne pourront pas être nommés correctement.
__Et enfin, les références qui sont dans les menus de SDP vierge en A et B n'existe pas toutes dans la feuille PrixMTX (renvoie #N/A dans ce cas en E )

Revenez si besoin de complément

crdlmnt
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
<... en complément du message précédent;, il est possible de mettre en place une solution conservant en permanence la formule en E. Il faudrait:
__transférer la fonction de la colonne C hors champ vers une colonne à droite.
__utiliser la colonne C pour la validation de E
__Modifier la formule en E pour que'elle affiche soit la valeur de la sélection en C, soit la valeur trouvée en cas de référence unique

crdlmn t
>
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021

Super merci pour votre réponse très complète! Cependant quelques petites précisions... Je n'avais pas suffisamment expliquer certains points je m'en rend compte au vue de vos réponses et remarques :
- vous dites que certaines données ne sont pas groupées, c'est à dire Trier de A à Z par colonne A, puis B afin de les rassembler par catégories?

- Le tableau en PrixMTX est très incomplet pour le moment et sera susceptible d'évoluer régulièrement même une fois plus remplie. C'est pourquoi :
* les réf en A et B de l'onglet SDP n'existent pas toutes en PrixMTX pour le moment.
* l'erreur N/A renvoyé dans ce cas n'est pas gênante, elle m'indiquera que je dois compléter mon tableau.

- Du coup peut-on automatiser le nommage des plages de la colonne C de PrixMTX? car il serait compliqué de les reprendre à chaque nouvel ajout!
C'est pourquoi initialement je suis passé par l'onglet xData où j'ai donné des numéros correspondant aux catégories au lieu des noms. Ainsi mes listes de sous catégories sont nommées categorie1, categorie2, ... et j'ai la place d'avance pour 10 sous catégories par catégories et jusqu'à 40 catégories. Ma méthode et la disposition de mon tableau ne sont peut être pas optimale.... J'attends votre proposition.

J'ai réussi à reproduire les différentes étapes sur mon fichier. J'ai décaler la colonne C de SDP en AA. J'ai revalider les formules matricielles une par une après avoir étendue la formule (je ne sais pas si c'était nécessaire).
Par contre j'ai remarqué que quand c'est une référence unique ça m'affiche un texte sans rapport (le texte de la colonne B de PrixMTX de la ligne juste en dessous), en revanche je retrouve bien ma référence unique dans la liste déroulante quand je clique dessus. Que faire pour éviter ça?


Ci dessous le lien du fichier excel d'exemple avec les modif que j'ai faites (je n'ai pas trouvé pour mettre en lien hypertexte) :
http://cjoint.com/?0AtmeOkAPjr
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
... site de ma réponse précédente
Après réflexion, je croit comprendre ce que vous voulez faire en colonne C, soit donc composé les libellés en fonction de votre tableau de DATA.
Si c'est bien cela, autant dire que ça va être un chantier excessivement complexe, (voire inutilisable) pour des données qui sommes toutes, vont rester statiques une fois entrées.
D'autant plus qu'il faut nommer chaque champ "composé" selon les titres A&B

Ce que je vous conseille:deux options
1° option
pour entrer une nouvelle ligne dans une composition A&B existante:
insérer une ligne au milieu de la liste existante, les limites de champs nommés vont se s'agrandir recaler automatiquement en conséquence, à condition de toujours insérer une ligne au dessous de la première et au dessus de la dernière (si vous n'avez que deux lignes, insérer sur la dernière)

2° option
Laisser dans le tableau suffisamment de lignes vides pour chaque composition A&B, et nommer les champs incluant ces lignes vides. Ainsi vous pouvez rajouter aux endroits voulus

A vous devoir, mais je ne pense pas qu'on puisse se lancer dans le remplissage automatique de la colonne C dans les limites du raisonnable... (sauf peut être en VBA , et encore,mais ce n'est pas dans mon domaine)

Reste donc à protéger si vous voulez la colonne E de SDP comme proposé.

crdlmnt

Errare humanum est, perseverare diabolicum
Nos réponses ce sont croisées, je n'avais pas vu ce dernier post.
Je ne suis pas sûre de bien comprendre si vous avez la bonne vision de mon problème, peut être mon dernier poste vous aura éclairé d'avantage?

Je pensais qu'il serais possible de nommer mes plages de la colonne C de PrixMTX (et non le texte présent dans la cellule), en fonction des colonnes A et B (en rajoutant une colonne de concatenage s'il le faut) ou à l'aide des fonctions comme DECALER et RECHERCHE ... en allant chercher dans l'onglet x-Data...

Sur mon onglet xData j'avais tenté de commencer à m'affranchir du nom des catégories en les remplaçant par des numéros (pensant que ça serait plus simple pour faire des recherches dans le tableau Data en fonction du numéro de position renvoyé)...
Et en prédéfinissant une zone avec 40 catégories et 10 sous catégories pour chacune....

Comme ça si je cherche la valeur de la colonne A de PrixMTX (BETON par exemple) dans mon tableau xData en fonction du n° de ligne je détermine le n° et donc le nom générique de la catégorie (categorie1).
Puis pareil pour la colonne B.

J'aimerais bien protéger la colonne E de SDP comme proposé.

Je continue à tester des solutions, à cout de fonctions de Recherche, Equi, Index ... Mais je ne maitrise pas très bien ces fonctions... Je débute avec.

Merci pour votre aide j'ai déjà bien avancé sur le problème.
Bonjour,

Donc pour répondre à vos questions :

Rassembler par catégorie = ok

Automatiser le nommage de la,colonne C de PrixMTX
Pour pouvoir afficher dans la colonne E de SDP mes listes de DesignationMTX en fonction des catégories et sous catégories, ça demande au préalable de donner des noms de plage. Par exemple nom "BETONLivré" pour C6:C9 de l'onglet Prix MTX, etc ...
Donc comment peut-on automatisé ça, ou l'éviter par un autre moyen? car mon tableau sera évolutif!

Problème référence unique décalée= ok corrigée

Code VBA pour rafraichir les valeurs en cas de changement
ça me semble très intéressant pour éviter les erreurs! mais je vais encore avoir besoin de votre aide sur ce point... car en VBA je tâtonne! J'ai cherché sur internet et j'ai néanmoins réussi à trouver comment exécuter une macro automatiquement en cas de changement de sélection, mais pour le moment j'affiche seulement un message (en cas de changement dans la plage A30:A50)! car je ne sais pas comment rafraichir ou effacer le résultat.

validation de l'info de E en colonne C au lieu de E
Au lieu de formater le texte en blanc ou autre pour le rendre invisible, j'ai préféré déplacer en colonne AB.

Précision du but de la colonne E
Le but de la colonne E est par la suite de me permettre de renvoyer automatiquement le prix du matériaux en colonne J.
A savoir également, l'onget SDP me servira de modèle pour dupliquer à l'infini pour faire des fiches individuelles.

Le fichier modifié ci-dessous:
http://cjoint.com/?0AtpnIze2L6
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
Re

je n'ai pas pour l'instant de solution pour éviter de nommer les champs au clavier au fur et à êsure que vous allez les créer. Je vois si on peut envisager d'organiser le tableau autrement
Toutefois je pense que nous ne nous sommes pas compris:

d'une part sur la façon d('organiser le tableau PrixMTX pour limiter le travail quand vous "allongez" la liste
d'autre part, sur l'utilité de ce que je vous propose pour la colonne C. Je suppose que votre feuille SDP vierge est destinée à servir plusieurs fois et donc il serait bon d'éviter la destruction des formules.

Donc, je vous propose de me laisser mettre en place un fichier représentatif de tout ça, y compris le VBA, et je vous en reparle demain.
crdlmnt
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
... suite
voila ce que ça pourrait donner.

https://www.cjoint.com/c/EAttyVzYcIp

J'ai créé la feuille LISTE pour automatiser la répartition automatique des désignations selon les rubriques.Vous pouvez donc rajouter des lignes dans la feuille PrixMDX sans les classer.

Il y a des explications un peu sur les quatre feuilles et quelques "tuyaux" pour simplifier et nommer rapidement

Attention, il n'y a pas dans ce système à intervenir dans la, colonne E de STD vierge, mais dans la, colonne C (vous pourriez protèger les cellules à formules par sécurité)
revenez si besoin
crdlmnt
Bonjour,

Encore merci pour tout le travaille et le temps passer pour m'aider!
J'ai tenté de tout décortiquer et reproduire sur mon fichier! Et ça marche nikel!
Bon j'avoue j'ai pas compris toutes les formules au point d'être capable de les reformuler seule! Mais j'ai bien compris le fonctionnement générale.
J'ai même réussi à déjouer le petit piège qui s'était glissé dans la macro à cause d'une faute de frappe.
Je compte adapter la méthode à une autre partie de mon fichier. C'est en persévèrent que l'on apprend!

J'ai prévu de verrouiller les cellules avec formules de mon onglet SDP. J'ai même fait en sorte que le mode plan soit accessible pour masquer/afficher certaines lignes/colonnes.
Par contre lorsque je duplique et renomme mon onglet ça ne fonctionne plus sur le nouveau. (tant que je n'ai pas fermé le fichier ça fonctionne, mais dès que je réouvre ça ne marche plus. Mais c'est logique vu que mon code VBA nomme précisément l'onglet :

Private Sub Workbook_Open()
With Worksheets("SDPvierge")
.EnableAutoFilter = True
.EnableOutlining = True
.Protect Contents:=True, Password:="toto", UserInterfaceOnly:=True
End With

End Sub


Existe-t-il un code pour le classeur entier?
car mon onglet pouvant être dupliquer à l'infini, je ne peux pas m'amuser à dupliquer le code avec le nom de chaque nouvel onglet!

Merci de bien vouloir m'aider à résoudre ce dernier problème...
Bonjour,

ça ne semble pas fonctionner... toute fois je ne suis pas totalement sûre de l'emplacement :
- ligne avant With .... ?
- ou derrière With sur la ligne (With Sheets([A1].Value).Activate) ?

J'ai testé les 2 sans succès.
Dans le 1er cas rien de particulier.
Dans le 2ème j'ai un message d'erreur à l'ouverture du fichier "Erreur d'exécution 424" et ça bloque dès la 1ère ligne suivant With ( .EnableAutoFilter = True).

Merci
Cordialement,
Non la macro ne fonctionne pas. Après je parlais de la formule en A1 permettant d'afficher le nom de l'onglet!
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
Et que voulez vous que fasse exactement cette macro.? et quand voulez vous que cela se fasse?
(le libellé que vous avez présenté ne fait que de la protection)
J'avais mis cette macro dans ThisWorkBook pour qu'elle s'exécute à l'ouverture du fichier.
Je veux que cette macro me permette de laisser accessible le mode plan pour grouper ou dégrouper certaines lignes ou colonnes définies, même lorsque ma feuille est protégé. Et cela pour toutes les feuilles du classeur, car je vais dupliquer mon onglet ).
Mais s'il existe un autre moyen que la macro je suis preneuse!
Messages postés
26192
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
22 septembre 2021
6 061
Pour ma part je ne connais pas exactement les libellés qui régissent ces options, et le menu des options d protection ne donne pas ces choix.
par rapport à ce que vous avez soumis, j'ai testé ça qui semble fonctionner:

Sub PRO()
ActiveSheet.Protect toto, EnableAutoFilter = True, EnableOutlining = True, UserInterfaceOnly:=True
End Sub


Cette macro s'adresse dans tous les cas à la feuille sélectionnée, il n'est donc pas utile d'y placer le nom de feuille.

pour l'appliquer sur chaque feuille, vous pouvez lui affecter une commande clavier, mais ceux qui connaîtrons la commande clavier pourront déverrouiller la feuille!
(c'est à mon avis aussi sur que Toto

commande possible avec ctrl + shift + lettre au clavier
crdlmnt
Bonjour,
Je reviens vers vous un peu tardivement, mais je n'arrive pas à faire fonctionner la macro. Ou doit elle être placée?
Ci-joint le fichier de la dernière fois où j'ai ajouté quelques groupements en mode plan et protégé la feuille (sans mot de passe).

http://cjoint.com/?0AxkIAu0nkj

Pouvez-vous svp le reproduire sur le fichier?
J'ai essayé dans le code de la feuille modèle SDP vierge et dans le Workbook Open, mais sans succès donc il doit y avoir quelque chose que je ne fais pas comme il faut.
De plus le toto dans votre code me semble bizarre... ne faut-il pas des guillemets pour le MDP?
Merci
Merci pour votre réponse et votre réactivité.
Je viens de reproduire avec succès.
Mais du coup je viens de comprendre que c'est juste une macro pour déverrouiller la feuille rapidement... sans avoir à taper le mot de passe. Et parès la feuille est déverouillé si on ne la reprotège pas...

Du coup à force de farfouiller et de comprendre un peu mieux le fonctionnement, j'ai eu une idée! et ça marche!
J'ai repris le code que j'avais initialement utilisée dans le Workbook Open, pour le mettre dans ma feuille modèle et qu'il s'exécute à la sélection de la feuille.
J'ai modifié la 1ère ligne pour qu'il utilise la feuille active ...
J'ai vérifié en la dupliquant et tout fonctionne!

Je met le code ci-dessous pour info, ça peut toujours servir à d'autres :

Private Sub Worksheet_Activate()

With ActiveSheet
.EnableAutoFilter = True
.EnableOutlining = True
.Protect Contents:=True, Password:="toto", UserInterfaceOnly:=True
End With

End Sub