Pierre Fauconnier

Salut.

La question d'une liste de validation intuitive est de temps en temps posée sur nos forums. Avant la version Excel 365, il fallait obligatoirement du VBA est une manipulation particulière plantait le code, obligeant à gérer l'erreur d'exécution.

Avec Excel 365, la donne a changé, et il maintenant possible, grâce aux plages dynamiques, de mettre en place une liste de validation intuitive qui "suit" la saisie pour adapter la liste possible. Pourquoi s'en priver surtout que l'on va voir que c'est très simple à mettre en place.

Même si son comportement n'est pas exactement le même que celui d'un contrôle de type ComboBox, l'ergonomie mise en place n'en est pas moins intéressante.

Cette liste de validation intuitive va s'appuyer sur une plage dynamique. Depuis XL365, certaines fonctions renvoient une plage dont la taille est dynamique et modulable grâce aux paramètres de la fonction.


Plages dynamiques

L'illustration suivante met en évidence la plage dynamique créée par la fonction FILTRE avec la formule

Code excel :

Sélectionner tout - Visualiser dans une fenêtre à part

=FILTRE(t_Contacts[Prénom];t_Contacts[Service]=F1)

Les conditions doivent être rédigées de façon à renvoyer VRAI et peuvent être formulées, ce qui offre une grande souplesse de filtre, puisqu'en gros, c'est votre capacité à formuler qui fixe la limite

Voici la fonction FILTRE pour récupérer les contacts travaillant dans un des services du tableau des services grâce à la formule

Code excel :

Sélectionner tout - Visualiser dans une fenêtre à part

=FILTRE(t_Contacts[Prénom];SIERREUR(EQUIV(t_Contacts[Service];t_Services[Services];0);FAUX))

On pourra donc filtrer les noms qui contiennent une certaine lettre ou un certain groupe de lettres grâce à la formule

Code excel :

Sélectionner tout - Visualiser dans une fenêtre à part

=FILTRE(t_Contacts[Prénom];SIERREUR(CHERCHE(F1;t_Contacts[Prénom])>0;FAUX))

La plage étant dynamique, on n'en connait ni la taille ni l'adresse complète. Mais Excel nous offre la possibilité de travailler avec en suffixant l'adresse de sa première cellule avec #. Dès lors, on peut la manipuler comme n'importe quelle plage d'une feuille. Ici, on va en compter les lignes, par exemple.




Liste de validation intuitive

Dès lors, on pourrait appuyer la liste de validation sur cette plage D1#.




Du coup, notre liste de validation s'adapte à la saisie partielle dans la cellule, à condition que l'on ait désactivé l'alerte pour saisie non conforme dans le troisième onglet de la boite de dialogue!





Si l'on veut tout ce qui commence par la saisie, il suffit de modifier la condition de FILTRE. On voit ainsi que c'est la formule qui pilote la création de la plage dynamique et donc de la liste de validation




Voilà une raison de plus de passer à Excel 365 si ce n'est déjà fait.