Source liste validation de données dynamique en fonction d'un critère

Bonjour à tous,

Je fais appel à vous pour m'aider sur un fichier Excel car malgré mes recherches je n'ai pas trouvé la solution dans les forums existant.

Explication de ce que je souhaite :

je souhaite réaliser un tableau dans lequel je saisi manuelle une référence dans la cellule A2 et en fonction de cette référence, Excel me propose une liste déroulante de choix dans la cellule B2.

Cette liste déroulante est basée sur un autre tableau regroupant dans la colonne A l'ensemble des références possibles (plusieurs milliers) et dans les colonnes B à K l'ensemble des choix possibles pour chaque référence (pas de vide dans les choix possibles)

Les colonnes B à K peuvent être ou pas renseignées donc une référence peut avoir entre 1 et 10 choix possible.

Je souhaiterai donc avoir cette liste de choix en cellule B2.

Vous trouverez ci-dessous un fichier exemple avec :

- dans l'onglet "Synthèse" les colonnes A et B uniquement (A étant celle ou je saisi les références manuellement et B celle ou je veux obtenir la liste de choix)

- dans l'onglet "BDD valeurs" la base de donnée avec en colonne A une liste de références et dans les colonnes B à K la ou les valeurs associées à chaque référence et que je souhaite retrouver dans la liste de choix de la colonne B de l'onglet Synthèse

Merci d'avance pour votre aide.

Cordialement,

Anthony

Bonjour

Le souci c'est que tu as des colonnes vides donc la liste comporte des trous si on travaille par formule.

Si on veut supprimer les trous il faut passer par PowerQuery

J'ai mis les 2 solutions dans le classeur

Bonjour Chris,

Merci beaucoup pour ton aide.

J'ai essayé de comprendre comment tu avais fait et de ce que je vois tu es passé par le gestionnaire de noms dans lequel tu as créé "ListeV" et "ListeV2".

J'ai vu que tu avais mis une formule dans la référence de ces deux noms et c'est ça que j'avais essayé de mettre à la base directement dans l'outil de validation de donnée mais sans succès. Je pense que je n'avais pas réussi non plus à écrire cette formule. J'ai essayé de la comprendre mais si tu as 5 min pour me l'expliquer ça serait top comme ça je pourrais le refaire facilement à l'avenir.

Dernière question, comment as-tu fait pour créer le tableau BDD Bis car la solution 2 me va bien mais je ne sais pas comment obtenir ce tableau. C'est de ça dont tu parles quand tu mets PowerQuery ?

Merci encore pour ton aide.

Cordialement,

Anthony

Bonjour

La formule

=DECALER(BDD[#En-têtes];EQUIV(Saisie[@Référence];BDD[Liste des référénces];0);1;1;NBVAL(BDD[#En-têtes])-1)

• part de la ligne d'en-tête du tableau BDD, (quelle que soit sa largeur, ce qui est donc évolutif)
• cherche la bonne ligne en parcourant la 1ère colonne pour y trouver la valeur en colonne Référence du tableau Saisie au moyen de la fonction EQUIV
• se décale d'une colonne pour ne pas prendre la colonne Référence du tableau BDD,
• limite la hauteur à une ligne et la largeur à celle du tableau BDD moins 1 colonne (nombre de colonnes calculé par NBVAL)

La 2ème solution utilise PowerQuery pour éliminer des listes déroulantes les valeurs inexistantes.

On charge le tableau dans PowerQuery (depuis une de ses cellules, Données, A partir d'un tableau)

On décroise le tableau pour n'avoir qu'une colonne de valeurs, on filtre les vides et on le charge dans un onglet

La formule

=DECALER(BDD_1[[#En-têtes];[Valeur]];EQUIV(Saisie[@Référence];BDD_1[Liste des référénces];0);;NB.SI(BDD_1[Liste des référénces];Saisie[@Référence]))

• part de l'ent-ête de la colonne Valeur de BDD_1
• cherche la bonne ligne en parcourant la 1ère colonne pour y trouver la valeur en colonne Référence du tableau Saisie au moyen de la fonction EQUIV
• calcule la hauteur avec NB.SI ( nombre fois ou Référence du tableau Saisie est listé)

Bonjour Chris,

Merci beaucoup pour ton aide et ton explication très précise.

J'aurai une dernière question pour toi :

Est-ce qu'il serait possible de mettre par défaut, dans la cellule contenant la liste déroulante, la première valeur de la liste et si l'utilisateur voit que pour des raisons techniques il faut changer pouvoir toujours avoir la liste déroulante contenant l'ensemble des possibilités.

Cela éviterait à l'utilisateur de devoir renseigner forcément chaque cellule mais uniquement celle ou la valeur doit être modifiée.

Merci encore pour ton aide.

Cordialement,

Anthony

Bonjour

Une cellule ne peut contenir une formule ou une valeur : c'est l'un ou l'autre

La seule solution est de coder en VBA : chaque fois qu'on saisit une référence, le code rempli la colonne B...

C'est bien ce que je pensais.

Merci pour toutes ces infos et pour ton aide.

Bonne journée,

Anthony