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

Difficulté: ToqueToque 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:

Champ Elastique 01

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”:

Champ Elastique 08

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

Champ Elastique 09

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

Champ Elastique 02

Champ Elastique 04

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

Champ Elastique 03

 

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:

Champ Elastique 05

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.

DECALER

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:

Champ Elastique 06

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:

Champ Elastique 07

 

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):

Champ Elastique 11

 

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

Champ Elastique 10

Une fenêtre s’ouvre:

Champ Elastique 12

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:

Champ Elastique 13

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

Champ Elastique 14

Finalisez en cliquant sur OK.

Le résultat est le suivant:

Champ Elastique 15

 

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:

Champ Elastique 18

 

4 thoughts on “Comment utiliser un champ dynamique dans une liste déroulante ?

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *