Excel : Liste déroulante en cascade sans plage nommée

Document initial créé par Raymond PENTIER

Liste déroulante de cellule en cascade
sans plages nommées

CCM a déjà publié plusieurs fiches techniques relatives aux listes déroulantes de cellules, simples ou en cascade. Ces dernières nécessitent l'utilisation de plages nommées, ce qui interdit les espaces, tirets et caractères réservés dans le nom de ces plages, donc pour les items de la liste déroulante principale.

Objectif

Il est alors particulièrement intéressant de se tourner vers une méthode qui remplace les plages nommées par des formules nommées où ces contraintes n'existent plus, et où les listes déroulantes sont dynamiques (peuvent changer de longueur).

Fichier support

Pour suivre nos explications, il est vivement recommandé d'ouvrir le fichier Excel https://www.cjoint.com/c/GBsxYQHg72H

Formules utilisées

Inspirées des travaux de Jacques Boisgontier, et adaptées au cas des listes en cascade par Via55, les 3 formules utilisées s'appuient sur la fonction DECALER, que nous n'allons pas décortiquer ici.
La feuille BDD contient en A la liste principale ; la colonne B calcule le nombre d'items de chacune des listes secondaires, qui occupent les colonnes C et suivantes :

=NBVAL(DECALER(C:C;;LIGNE()-2))-1

En Feuil3 la liste déroulante principale est en B2, et la source de la validation est :

=DECALER(BDD!$A$2;;;NBVAL(BDD!$A:$A)-1)

qui s'adapte automatiquement au nombre d'items saisis en colonne A de BDD et la liste déroulante secondaire en D2 a
pour source de validation :

=DECALER(BDD!$A$2;;EQUIV(choix;ListeA;0)+1;RECHERCHEV(choix;BDD!$A:$B;2;0))

,
dont la longueur de liste est indiquée par la deuxième colonne de BDD.
N.B. Quand on change de choix en B2, l'item en D2 ne disparaît pas spontanément ; il faudrait une macro pour cela ; mais cela pourra faire l'objet d'une autre fiche ...

Autres ...

autres avantages

- On peut glisser à d'autres emplacements les cellules B2 et D2, contenant les listes déroulantes.

- On peut changer à volonté le nombre et le libellé de toutes les données de BDD.
- On peut librement ajouter, modifier ou supprimer des items en colonne A de la feuille BDD : les intitulés des listes secondaires, en ligne 1, se modifient en conséquence.