XL 2016 Liste intuitive dans menu déroulant

[com689]

Bonjour à tous,

Je suis en cours de création d'un fichier d'audit, et dans un soucis de praticité, je souhaiterais que certaines cellules se remplissent selon un choix limité de réponse, contenues dans une cellule avec menu déroulant.
Je rencontre actuellement deux problèmes:

Tout d'abord, j'ai essayer de faciliter la saisie avec une liste semi intuitive, dans les cellules concernées (cellules G6 à G....), malheureusement cela ne fonctionne pas.
dans l'onglet BDD, colonne Q:Q, j'ai saisie cette formule : "=SI(NB.SI(R1;"*"&'Les extérieurs'!$G$6&"*")>0;MAX($Q$1:Q1)+1;"")"
Afin que suivant les lettres saisies en cellule G..., une numérotation s'incrémente en fonction des correspondances et donc pouvoir récupérer et trier ces correspondances dans d'autres cellules, mais malheureusement, quel que soit le caractère saisie, cette colonne reste désespérément à 0, et je ne comprends pas pourquoi.
Pourriez-vous m'aider sur ce sujet?

L'autre problème sur lequel je bloque est que mon fichier contient plusieurs onglet (j'ai joint une version allégée), et je souhaiterais que plusieurs de ces onglets bénéficient de cette saisie intuitive, mais pas tous. il y a entre 15 et 40 onglets, dont environ 7 environ ne doivent pas avoir de liste déroulantes semi intuitive.
A ce jour, je n'imagine même pas comment je pourrais quasi généraliser cette formule "=SI(NB.SI(R1;"*"&'Les extérieurs'!$G$6&"*")>0;MAX($Q$1:Q1)+1;"")", sans passer par des OU:
=SI(NB.SI(ou(R1="*"&'Les extérieurs'!$G$6&"*";R1="*"&'La réception'!$G$6&"*";R1="*"&'Les chambres (Bât.1'!)'!$G$6&"*";....;.......);>0);MAX($Q$1:Q1)+1;"")

Existe-t-il une solution plus simple ou plus académique?

J'aimerais autant que possible éviter d'alourdir le fichier, j'ai du coup écarté la solution VBA avec des menu déroulant de type contrôle, au vu du nombre nécessaire (un petit millier suivant le fichier.

Merci d'avance de votre aide.

Mickaël.

 

[sylvanu]

Bonjour Com,
Vous avez une bonne approche de liste déroulante intuitive sans VBA sur ce lien.
Je vous met en PJ le fichier concerné, les listes sont en colonne A.

 

[com689]

Bonjour Sylvanu,

Merci pour ce retour.
Cependant, je souhaiterais que la recherche se fasse sur la suite des caractères saisis, quels que soit leurs position dans les chaines de caractères, en clair, si je saisie ois, la liste devrait afficher :
"Framboises (100g)" ; "Lait de noisettes (100g)" ; "Pois chiches (100g)".
J'ai essayé de modifier la validation de données de la liste déroulante comme suit, "=DECALER(Nom;EQUIV("*"&A20&"*";Nom;0)-1;;NB.SI(Nom;"*"&A20&"*"))"
mais cela ne fonctionne pas.

Auriez-vous une idée du pourquoi?

Merci d'avance,

 

[sylvanu]

Bonsoir, je pense que ...
Dans la formule initiale on fixe un départ (equiv) et une taille (nb.si).
Cela donne une plage continue d'éléments contigus.
Dans votre formule vous recherchez des éléments non contigus, par ex "na" est en ligne 6,9,31,44 ...
Je ne vois pas comment par formule on peut s'en sortir.
Par ex dans la PJ #2, j'ai mis en liste déroulante :

=DECALER(Nomaliments;EQUIV("*"&A20&"*";Nomaliments;0)-1;;NB.SI(Nomaliments;"*"&A20&"*"))

et sur "na" il me répond :

Pourquoi ? parce que Equiv donne 5 et NB.si 3, donc il donne la liste de 3 éléments à partir de la position 5.
Donc si les éléments ne sont pas consécutifs, ça ne peut pas marcher.

 

[com689]

Merci pour ce rapide et très clair retour.
Je continue mes investigations pour trouver une solution à mon problème.

 

[sylvanu]

Peut être avec du VBA .... et ce ne serait pas facile à faire.

 

[com689]

Ma crainte est qu'avec du VBA, je vais être obligé d'avoir une multitude de combobox (jusqu'a 900), et cela risque d'alourdir considérablement mon fichier?