liste11Qui n’a pas déjà fait une liste déroulante dans une cellule d’Excel ? C’est simple avec la validation de données, je choisis Liste et je tapes mes valeurs en les séparant d’un point virgule. Mais nous pouvons aller plus loin en utilisant pour la liste de choix :

  •  
  • une plage située sur une autre feuille,
  • une colonne d’un tableau,
  • une plage dont l’emplacement dépend du choix fait dans une précédente liste.

Une plage située sur une autre feuille

liste01

L’astuce consiste à nommer la plage source :

  1. sélectionnez votre plage,
  2. dans la zone Nom (située à gauche de la barre de formule), tapez un nom pour votre plage,
  3. validez ce nom avec la touche Entrée,
  4. sélectionnez la cellule devant contenir la liste de choix,
  5. sur l’onglet Données, groupe Outils de données, cliquez sur le bouton Validation de données.
  6. choisissez Liste dans la zone Autoriser,
  7. placez votre curseur dans la zone Source et appuyez sur la touche F3. Cette dernière affichera la liste des noms définis dans le classeur. Sélectionnez votre nom et Ok.
  8. votre liste déroulante est prête.

liste02

Attention !

Lorsque vous ajoutez une donnée dans la liste source, ajoutez-la à l’intérieur de la plage existante et non à la fin.
Sinon il vous faut recourir à la fonction DECALER pour créer une plage dynamique ainsi :

Cliquez sur le bouton Définir un nom de l’onglet Formules, groupe Noms définis

liste04

Puis tapez un nom dans la zone Nom et la formule indiquée ci-dessous (sans oublier le égal) dans la zone Fait référence à

=DECALER(Feuil2!$D$2;0;0;NBVAL(Feuil2!$D:$D)-1)

Ma liste commence en D2 et bien entendu, je n’ai rien en-dessous de cette liste.

liste06

Utilisez ce nom défini en source de votre liste. Vous pouvez maintenant ajouter une donnée sous votre liste source, elle sera prise en compte dans la liste déroulante.

Une colonne d’un tableau

Depuis Excel 2007, on peut se référer à une colonne d’un tableau en indiquant le nom du tableau et de la colonne ainsi :
=nom_tableau[intitulé de votre colonne]

Hors si vous essayez d’utiliser ce système dans la source d’une liste, cela ne fonctionne pas. Mais en définissant un nom sur cette formule, tout fonctionnera à merveille.
Procédez comme ci-dessus pour créer un nom, mais cette fois-ci utilisez la formule :

=nom_tableau[intitulé de votre colonne]

liste05

Utilisez maintenant votre nom comme dans l’étape “une plage située sur une autre feuille” ci-dessus.

Le gros avantage de cette méthode est l’extension automatique de votre liste lors de l’ajout d’une donnée dans votre tableau.

Une liste dépendant d’une autre

Au préalable, vous devez avoir 2 listes sources :

  • la 1ère pour faire la 1ère liste. Exemple :
    liste01
  • Puis une liste ordonnée (tri croissant ou décroissant sur la 1ère colonne) qui indique pour les choix de la 1ère liste, ce qui doit s’afficher dans la 2ème liste. Par exemple :

liste07

Puis procédez ainsi :

  1. Nommez la 1ère liste source comme dans l’étape “une plage située sur une autre feuille” ci-dessus.
  2. Nommez la cellule contenant la liste déroulante. Par exemple : “Choix1”
  3. Créez un nom défini pour la 2ème source en utilisant la formule suivante (en considérant que la plage ci-dessus se situe à partir de la cellule C1) :
    =DECALER(Feuil2!C1;EQUIV(Choix1;Feuil2!C:C;0)-1;1;NB.SI(Feuil2!C:C;Choix1);1)

DECALER permet de récupérer une plage à partir d’une cellule ou d’une plage en indiquant :

  • la plage de référence
  • le décalage vertical.
    EQUIV permet de récupérer la position de la 1ère occurrence de votre choix1. Nous soustrayons 1 à cette position car nous voulons la valeur du décalage et non de la position.
  • le décalage horizontal, ici 1 (pour avoir la 2e colonne)
  • le nombre de lignes en hauteur à récupérer
    NB.SI permet de récupérer le nombre d’occurrences de votre choix1
  • le nombre de colonnes à récupérer, ici 1 (nous ne récupérons qu’une colonne)

Vous pouvez tester le fonctionnement de ces 2 listes de choix dans ce classeur en téléchargement

Cathy MONIER, MVP Excel
Auteur du site CathyAstuce