liste

Liste dynamique

30/07/2014

Lorsque l’on crée une liste déroulante sur excel pour limiter le choix de valeurs à l’utilisateur, celle-ci se base sur un nombre limité de valeurs (une plage). Si nous souhaitons que la liste s’adapte à plus de valeurs insérées, il faut ainsi rendre cette plage dynamique.

Test 1

Pour créer une plage dynamique, il faut donc d’abord une plage source.

Dans notre exemple, il s’agit d’un tableau répertoriant différentes destinations de voyages.

Liste dynamique - Image 2

Pour créer un menu déroulant classique en cellule D2 à partir de cette liste, aller dans l’onglet Données puis cliquer sur Validation de données. Choisir dans le premier menu déroulant « Liste » et sélectionner ensuite la plage du tableau.

Liste dynamique - Image 3

Ainsi, nous aurons dans cette cellule un menu déroulant correspondant à notre liste.

Toutefois, si nous souhaitons ajouter une destination à cette même plage, le menu déroulant ne prendra pas en compte cette modification et restera donc le même.

Afin d’éviter les actualisations successives de la liste dans la fenêtre Validation de données, nous allons donc créer une plage dynamique.

Pour cela, nous allons utiliser la fonction décaler qui permet de définir une plage en relatif par rapport à une cellule de référence.
Pour rappel, celle-ci comporte 5 paramètres :

Liste dynamique - image 41. « Réf » : cellule de référence : dans notre cas $A$1

2. « Lignes » : position relative de la première ligne de la plage par rapport à la cellule de référence. Dans notre cas : 1 car nous allons commencer notre plage de données par la première destination qui se situe en ligne 2

3. « Colonnes » : position relative de la première colonne de la plage par rapport à la cellule de référence. Pour nous ce sera 0 car nous restons dans la même colonne A:A

4. « Hauteur » : nombre de lignes de la plage : celle-ci sera donc dynamique. Dans notre cas, elle est pour l’instant égale à 4 (nombre de destinations) mais si nous souhaitons ajouter une ligne, celle-ci sera égale à 5. Ce problème se résout facilement grâce à la fonction NBVAL (COUNTA en anglais) qui compte le nombre de cellules non vide d’une plage choisie. Ici, nous choisissons donc la colonne A qui comprend 5 valeurs non vides (le titre de la plage + les 4 destinations déjà rentrées), il faut donc soustraire 1 pour obtenir le nombre exact de destinations: NBVAL($A:$A)-1

5. « Largeur » : nombre de colonnes de la plage : celle-ci est fixe et égale à 1

Ainsi, au lieu de sélectionner à la main notre plage de données comme nous l’avons fait plus haut dans la fenêtre Validation de données, nous allons maintenant rentrer la formule suivante :
=DECALER($A$1,1,0,NBVAL($A:$A)-1,1)

(en anglais : =OFFSET($A$1;1;0;COUNTA($A:$A)-1;1) )

Liste dynamique - image 5

La plage sélectionnée est entourée de pointillés et après avoir cliqué sur « ok », notre menu déroulant s’adaptera instantanément à toute modification de notre plage. En effet, si nous ajoutons une ligne « Norvège » à notre tableau, nous constatons qu’en cellule D2, le menu déroulant possède bien cette nouvelle destination comme choix.

Liste dynamique - image 6