[XL-2007] Listes déroulantes dépendantes

[hegalaldia]

slt

J'ai suivis ce tuto de developpez pour creer une dependance entre 2 listes deroulantes. J'ai 2 questions.

1) Dans le tuto, il est dit

...
Saisissez dans la zone source :
=INDIRECT($D$2)
...

hors, la cellule contenant la liste principale est en D1. Pourquoi dans la formule il est mis D2 ??

2) Dans le tuto il est dit de nommer les plages de la 2eme liste avec la meme orthographe que les elements de la liste principale. Certain elements de ma liste principale sont avec plusieurs mots (par exemple Animal blesse). Hors les noms de plage ne doivent pas contenir d'espace. Et je ne veux pas mettre de tirets entre les mots dans ma liste principale. Cette methode est bien quant la liste principale ne contient pas d'element avec des "mots composes".

Je peux faire comment ?

Merci d'avance pour votre aide.

[hben1961]

Bonjour à tous,

Ajoutes le nom de plage ligne1 par la formule :

Code :

=Listes!$1:$1

et remplaçes la formule de la plage nommée Details par la formule:

Code :

=DECALER(INDIRECT("Listes!"&ADRESSE(2;EQUIV(Janvier!$E3;ligne1;0)));;;NBVAL(INDIRECT("Listes!"&ADRESSE(1;EQUIV(Janvier!$E3;ligne1;0))&":"&ADRESSE(100;EQUIV(Janvier!$E3;ligne1;0))))-1)

et changes la formule

Code :

=INDIRECT($E$3)

de la liste de validation dans la colonne Détail (Colonne F) par la formule

Code :

=Details

et comme ça on evitera les noms composés de noms de plages...

Cordialement

[hegalaldia]

Merci pour ta reponse.

J'ai pas compris le Ajoutes le nom de plage ligne1 par la formule

[hben1961]

Bonjour

Voir fichier attaché et les noms des plages nommées...

Cordialement

[matbiz]

Bonjour à tous,

J'ai à peu près le même problème que hegalaldia: les éléments de ma liste principale ont tous des tirets (-), ce qui m'empeche de les définir comme nom de plage.

J'ai essayé d'utiliser la formule de hben1961, mais je ne comprend pas vraiment comment elle fonctionne, du coup je n'arrive pas a la transposer a mon document.

A quelle plage de cellules correspond la plage nommée Détails?

Merci d'avance pour vos réponses

[hegalaldia]

Merci hben1961 pour le fichier.

matbiz, la formule correspond a la plage de cellules B2 a B6 de la feuille Listes (onglet rouge).

Moi c'est pareil je ne vois pas comment marche cette formule.

[hben1961]

Bonjour à tous,

Une petite explication du fonctionnement de la formule:

Code :

=DECALER(INDIRECT("Listes!"&ADRESSE(2;EQUIV(Janvier!$E3;ligne1;0)));;;NBVAL(INDIRECT("Listes!"&ADRESSE(1;EQUIV(Janvier!$E3;ligne1;0))&":"&ADRESSE(100;EQUIV(Janvier!$E3;ligne1;0))))-1)

On commence par la partie EQUIV(Janvier!$E3;ligne1;0) qui se répète dans la formule... Elle cherche la position (numéro de colonne) de la valeur de la cellule Janvier!$E3 dans la première ligne de la feuille Listes (Supposons qu'elle donne la valeur 2) et la formule deviendra :

Code :

=DECALER(INDIRECT("Listes!"&ADRESSE(2;2));;;NBVAL(INDIRECT("Listes!"&ADRESSE(1;2))&":"&ADRESSE(100;2)))-1)

La partie ADRESSE(2,2) donne $B$2 , ADRESSE(1,2) donne $B$1 et ADRESSE(100,2) donne $B$100
Et la formule devient:

Code :

=DECALER(Listes!$B$2;;;NBVAL(Listes!$B$1:$B$100)-1)

qui donne la liste de toutes les valeurs des cellules renseignées de la colonne B de la feuille Listes à partir de la cellule $B$2

Cordialement

[matbiz]

Re-bonjour tout le monde,

hegalaldia j'ai peut être trouvé une solution qui te conviendra.

En fait la méthode consiste a imbriquer des SI dans la formule Source de la validation. C'est assez fastidieux à faire, mais ça marche. Malheureusement cette méthode est limitée a 7 imbrication de SI, et surtout le nombre de caractères de la formule Source de l'assistant validation est limité et donc je n'ai pas la place de rentrer mes 7 imbrications...

Quelqu'un saurait si c'est possible d'augmenter le nombre de caractères maximum de la barre de formule? (Je crois que je rêve un peu...)

Sinon je suis toujours preneur de la première solution si on me la déchiffre un peu, parce que je comprend pas comment l'appliquer.

[GerardCalc]

Bonjour,

Il y a une autre méthode (plus simple) qui est de combiner la fonction INDIRECT avec SUBSTITUE :

Nommer les listes avec un _ remplaçant les espaces, par exemple animal_blesse puis utiliser :

Code :

=INDIRECT(SUBSTITUE($E3;" ";"_"))

Si en E3 tu choisis animal blesse, SUBSTITUE remplace l'espace par un _ et donc renvoie la plage animal_blesse.

L'avantage de la formule de hben1961 est que la plage est dynamique, tu peux donc rajouter des noms aux sous-listes.

A+

[matbiz]

Ah! La formule parfaite, simple, facile a recopier partout sans se tromper avec les $, rapide! Et non je me suis emballé un peu vite... GerardCalc ta formule marche vraiment très bien, mais j'ai effectivement une plage dynamique , donc je ne peux pas l'utiliser. Merci quand même, elle servira peut être à hegalaldia

Je vais essayer de me plonger dans la formule de hben1961 pour la mettre en place, puisqu'apparemment elle correspond à mon besoin.

Merci à tous les deux

[matbiz]

Bonjour à tous,

J'ai déchiffré la formule de hben1961, mais je n'arrive définitivement pas à la transposer pour qu'elle fonctionne avec mes données.

J'ai essayé avec cette formule, mais ça me met toujours une erreur et rien dans la liste déroulante:

Code :

=OFFSET(INDIRECT("Equipments concordance!"&ADDRESS(2,MATCH($B7,CategoryEquip,0))),,,COUNTA(INDIRECT("Equipments concordance!"&ADDRESS(1,MATCH($B7,CategoryEquip,0))&":"&ADDRESS(1000,MATCH($B,CategoryEquip,0))))-13)

Mon problème est peut-être que j'ai trop de plages dynamique...

Vous pouvez consulter mon fichier à cette adresse pour comprendre comment il est construit.


Merci par avance de votre aide.

[hben1961]

Bonjour à tous, bonjour matbiz

J'ai ajouté deux colonnes pour les listes Description Unique et Brand Unique pour numéroter automatiquement les cellules non vides dans ces listes (en rouge dans la feuille 'Equipment concordance')... Les plages nommées DescriptionUnique et BrandUnique sont définies par les formules respectives:

Code :

=DECALER('Equipment concordance'!$H$14;;;MAX('Equipment concordance'!$G:$G))

et

Code :

=DECALER('Equipment concordance'!$K$14;;;MAX('Equipment concordance'!$J:$J))

La fonction MAX est utilisée pour simplifier les formules...

Voir le fichier attaché sur le lien http://www.mediafire.com/file/uz6hjv...le_exemple.xls

Cordialement

[matbiz]

Bonjour hben 1961,
Merci pour la modification, c'est vrai que ça modifie pas mal la formule et l'a rend un peu moins indigeste.

Par contre, il faut encore insérer ta formule pour que le tri se fasse dans la liste de validation en fonction du 1er choix effectué en B7. Je dois la mettre dans la formule de la plage DescriptionUnique, mais comment l'insèrer?

Cordialement

[hben1961]

Bonjour à tous, bonjour matbiz

Les modications que tu veux nécessitent la création des listes (Description et Brand) de chaque catégorie dans la feuille Concordance par les formules nécessires (le fichier est passé de 800 KO à plus de 5 MO...). Des feuilles de calcul ont été renommées simplement pour éviter que le nombre de caractères des formules utilisées pour les plages nommées DescritionUnique et BrandUnique ne dépasse le nombre 256 (limite)...

Voir fichier attaché au lien : http://www.mediafire.com/?37z0mc4g84q4734

Cordialement

[hegalaldia]

J'ai pas trop le temps de m'occuper de ce fichier. Donc je reviendrais voir tout ca en detail plus tard. Je vous tiens au courant.

Mais merci pour votre aide

[matbiz]

Bonjour à tous,
Merci beaucoup hben1961, tes formules et l'organisation du fichier correspondent vraiment à ce dont javais besoin. Je te remercie énormément pour ton aide.

Il y a juste un problème pour mon fichier: une marque peut être utilisée dans deux catégories. C'est le cas par exemple de Honda ou Yamaha qui sont à la fois dans la catégorie EQ-POW (générateurs) et EQ-VEH (motos). Ainsi ces deux marques apparaissent dans la liste des marques EQ-POW mais pas dans celle des EQ-VEH puisqu'elles sont déjà notée dans la première catégorie.

J'ai essayé de rajouter une fonction SI pour valider par le nom de catégorie, mais soit je n'ai pas trouvé le bon endroit pour la mettre, soit ce n'est pas cette fonction qu'il faut utiliser. Est-ce que quelqu'un aurait une idée?

Sinon, j'aimerais bien que les éléments de ma liste déroulante soient classés par ordre alphabétique, ça faciliterait la recherche. Pour ça j'ai créé une deuxième colonne qui met dans l'ordre les résultats de la première avec la formule:

Code :

=IF(SUM(1/COUNTIF(L$14:L$1000,L$14:L$1000))<ROWS(M$14:M14),"",INDEX(L$14:L$1000,MATCH(MIN(IF(COUNTIF(M$13:M13,L$14:L$1000)=0,COUNTIF(L$14:L$1000,"<"&L$14:L$1000),"")),IF(COUNTIF(M$13:M13,L$14:L$1000)=0,COUNTIF(L$14:L$1000,"<"&L$14:L$1000),""),0)))

Ca marche bien, pas de soucis, mais je me demandais s'il était possible d'avoir déjà le tri alphabétique dans la première colonne. Comment il faudrait faire? Sachant que la première colonne a pour formule:

Code :

=IF(ROWS($1:1)>MAX(J:J),"",OFFSET($C$14,MATCH(ROWS($1:1),J$14:J$1000,0)-1,0))

Merci par avance pour vos réponses éclairées

Cordialement

[matbiz]

Bonjour à tous,

Juste un petit message pour vous faire part de la solution que j'ai finalement opté pour résoudre mon problème:
J'ai extrait mes données "description" et "brand" dans 2 tableaux croisés dynamiques différents afin de ne les avoir qu'un seul fois chacun et dans l'ordre alphabétique. Ensuite j'ai définis des noms à ces deux listes pour pouvoir les utiliser dans mes formules de validation.
Pour finir j'ai appliqué ces différentes formules:

1ere validation: (liste figée, simple)

Code :

=MaListe

2e validation:

Code :

=OFFSET(Lists!$B$4,MATCH($B7,Lists!A:A,0)-4,0,COUNTIF(Lists!A:A,$B7))

Note: En B7 se trouve la cellule de la première validation.


3e validation:

Code :

=OFFSET(INDEX(BrandUnique,,4),MATCH($B7,INDEX(BrandUnique,,1),0)-1+MATCH($C7,OFFSET(INDEX(BrandUnique,,3),MATCH($B7,INDEX(BrandUnique,,1),0)-1,),0)-1,0,SUMPRODUCT((INDEX(BrandUnique,,1)=$B7)*(INDEX(BrandUnique,,3)=$C7)))

Notes: En C7 se trouve ma 2e cellule de validation. "BrandUnique" est le nom de mon 2e TCD.


4e validation:

Code :

=OFFSET(INDEX(ListEquip,,4),MATCH($C7,INDEX(ListEquip,,2),0)-1+MATCH($D7,OFFSET(INDEX(ListEquip,,3),MATCH($C7,INDEX(ListEquip,,2),0)-1,),0)-1,0,SUMPRODUCT((INDEX(ListEquip,,2)=$C7)*(INDEX(ListEquip,,3)=$D7)))

Notes: En D7 se trouve ma 3e cellule de validation. "ListEquip" est le nom de mon tableau de départ avec toutes les données (avec certains éléments en double, comme "brand" par exemple).


Mon fichier fonctionne très bien avec ces différentes formules qui ont plusieurs avantages:
- toutes les plages sont dynamiques
- aucune donnée en double dans les listes (pas de plages blanche non plus)
- et surtout la taille du fichier reste très petite. Avec la méthode de hben1961 (qui marche bien malgrés tout) mon fichier dépassait facilement les 2MO, alors que là mon fichier fait moins de 400KO

Voila voila pour la solution que j'ai utilisé. Merci à tous pour votre aide!

Je n'ai plus qu'un seul petit problème sur mon fichier: La 3e validation ne m'affiche pas d'erreur lorsque je rentre une donnée qui n'est pas dans la liste. J'ai bien vérifié je n'ai pas de cellules vides dans mes listes donc je ne sais pas d'où ça vient... Si quelqu'un à une idée je suis preneur!

[matbiz]

Bonjour à tous,

Je reviens sur ce post, car je suis de nouveau confronté à un problème pour mes listes de validation. J'avais créé initialement mon fichier sous Excel 2010, et la méthode que j'ai décrit dans le message précédent fonctionnait très bien. Mais en vue d'une diffusion du fichier sur des postes utilisant en majorité Excel 2003, jue dois réviser mon fichier, et du coup je me retrouve de nouveau au point de départ.

En effet, Excel 2003 ne supporte pas:
- les listes de validations avec des données venant d'autres feuilles,
- l'utilisation de la fonction indirecte avec une plage dynamique.

Or j'utilise des données venant d'une autre feuille, et des plages dynamiques. Je ne sais pas comment faire du coup.

Pour info: je tire mes données de TCD, elles sont donc ordonnées par catégories, mais dans une seule colonne. Les catégories sont les unes à la suite des autres.

Merci par avance de votre aide.