La formule =DECALER($A$5;0;0;NBVAL($A:$A)-1;1), à adapter selon les données, permet de retourner dynamiquement le contenu d'une plage de cellules dans une liste déroulante :
► la fonction DECALER renvoie une référence à une plage de cellules qui contiennent la liste à afficher ;
► $A$5 fait référence à la première cellule de la liste source ;
► laisser 0;0; pour n'appliquer aucun décalage horizontal ou vertical ;
► $A:$A correspond à la colonne (A) où se trouve la liste source ;
► NBVAL compte le nombre de cellules non vides dans cette colonne ;
► valeur -1 : on enlève 1 au nombre renvoyé par NBVAL si l'une des cellules contient une entête de liste, dont il ne faut pas tenir compte ;
► le dernier 1 correspond au nombre de colonnes de la référence renvoyée par DÉCALER (laisser la valeur 1).

On peut associer cette formule à un nom de plage que l'on utilisera ensuite pour créer une liste déroulante.

Pour ajouter un nom de plage, aller dans l'onglet Formules puis Gestionnaire de noms.

Dans la fenêtre qui s'ouvre, donner un nom à la plage de cellules dont souhaite voir afficher les valeurs dans la liste déroulante.

Dans la zone Fait référence à, copier-coller la formule vue plus haut, c'est-à-dire =DECALER($A$5;0;0;NBVAL($A:$A)-1;1)

Valider l'enregistrement en cliquant sur le bouton OK.

Sélectionner la cellule où l'on souhaite voir s'afficher la liste déroulante. Dans l'onglet Données, sélectionner Validation des données.

Dans la fenêtre qui s'ouvre, placer le curseur dans le champ source. S'il y a déjà du texte, il faut le supprimer pour ne laisser que le signe =.

Appuyer sur la touche F3. Une deuxième fenêtre s'ouvre avec la liste des noms de plages précédemment enregistrés. Sélectionner la plage qui nous intéresse.

Maintenant, si la plage de cellules est modifiée (ajout ou suppression de données), la liste déroulante se mettra automatiquement à jour.