Comment utiliser un champ dynamique dans une liste déroulante ?

Difficulté:  Intermédiaire

Ustensiles: Excel

Ingrédients: Validation de données, DECALER, NBVAL, Champs nommés.

Bases requises: Champs nommés

Description

L’objectif de cette recette est de créer une liste déroulante d’après une liste de données. La particularité est que si j’agrandie cette liste an ajoutant une donnée à la fin, la liste déroulante sera mise à jour automatiquement.  On utilisera un champ nommé (nom qu’on peut donner à un ensemble de cellules) dit élastique car la taille sera variable en fonction du nombre de valeurs.

Quand vous connaîtrez le champ élastique, vous ne pourrez plus vous en passer

Pour construire ce cas, nous partirons d’une liste de « Super Héros » comme le montre l’image ci-dessous. Vous pouvez voir que le nombre de valeurs possible est 4:

Voici dans le détail, chacune des étapes.

Etape 1: Ouvrir le gestionnaire de nom pour la création d’un champ nommé

Pour créer un champ nommé, vous avez de nombreuses façons. La plus connue étant de le créer en sélectionnant la liste (G6:G9) et de taper le nom souhaité en haut à gauche, juste au-dessus de la lettre A. Ici, nous l’avons appelé « Héros »:

En faisant CTRL + F3, nous pouvons voir ce champ nommé:

Toutefois, nous n’allons pas utiliser cette méthode pour créer le champ élastique.

Dans le Ruban: Formules > Noms Définis > Définir un nom

Vous pouvez également aller dans Gestionnaire de noms (Raccourci: CTRL + F3) et cliquer ensuite sur Nouveau.

Etape 2: Création du champ nommé

Après avoir cliqué sur « Définir un nom » ou « Nouveau », vous allez arriver sur l’écran suivant:

Dans Nom: tapez le nom de votre champ nommé (le nom de la liste). Ici, nous l’appelons « Liste_Héros ».

Dans Zone, laissez « Classeur ». Il s’agit de la portée du champ. En laissant classeur, nous pourrons l’utiliser dans toutes les feuilles du classeur.

Enfin, dans fait référence à, nous allons utiliser une formule permettant de définir une plage (ensemble de cellules) variable. Dans notre cas, elle devra être égale à « G6:G9 » mais si nous ajoutons un Super-Héros, elle devra devenir automatiquement « G6:G10 ». Notre plage dépendra donc du nombre de valeurs.

Pour cela, nous allons utiliser deux fonctions: DECALER et NBVAL.

DECALER

La fonction DECALER (OFFSET en anglais) sert à définir une plage de cellules dans la feuille excel en partant d’une cellule de départ, on décale la référence de cette cellule d’un certain nombre de lignes et de colonnes, ce qui définit la cellule en haut à gauche de la plage et enfin on indique la largeur et la hauteur de la plage.

NBVAL

Cette fonction (COUNTA en anglais) permet de compter le nombre de cellules non vides dans une plage.

Vous pouvez entrer la formule suivante dans « Fait référence à »:

=DECALER(Page1!$G$5 ; 1 ; 0 ; NBVAL(Page1!$G:$G)-1 ; 1)

=DECALER(Cellule référence ; Décalage en bas ; Décalage à droite ; Hauteur ; Largeur)

Dans notre exemple, voici comment nous allons imbriquer les fonctions:

Validez ensuite le champ nommé et fermé.

Vous remarquerez que le champ nommé nouvellement créé (Liste_Héros) ne peut pas être sélectionné en haut à gauche:

Etape 3: Création de la liste déroulante

Sélectionner tout d’abord la cellule dans laquelle vous souhaitez créer votre liste déroulante (Ici: $J$3):

Dans le Ruban: Données > Outils de Données > Validation de données

Une fenêtre s’ouvre:

Dans l’onglet Options, choisissez d’abord Liste puis dans Source tapez « = » puis tapez sur F3.

Une nouvelle fenêtre s’ouvre, proposant tous les champs nommés:

Cliquez deux fois sur « Liste_Héros » , cela vous ramènera à l’écran suivant:

Finalisez en cliquant sur OK.

Le résultat est le suivant:

De mon côté, j’ai donc également créé une autre liste déroulante se basant sur le champ nommé non dynamique « Héros » (cf.étape 1) pour vous montrer les différences entre les deux types de champs.

CONCLUSION

Si nous ajoutons un Super Héros à la fin de notre liste, dans la liste dynamique (la première), la liste est mise à jour automatiquement et pas dans l’autre: