Excel: Créer une liste de validation dynamique avec les données d’une autre feuille

Publié le 08 février 2016
par Sophie Marchand M.Sc., CPA, CGA, MVP
Liste s'allonge avec formule 2

Excel: Créer une liste de validation dynamique avec les données d’une autre feuille

Il existe plusieurs situations où créer une liste déroulante dans Excel via la fonctionnalité de validation de données est intéressante, notamment, lorsqu’on souhaite qu’un usager choisisse une valeur parmi une liste de valeurs permises. Pour obtenir d’autres exemples de l’usage de la validation de données et pour savoir comment l’utiliser, vous pouvez relire l’article Astuce Excel: Validation de données (Data validation). L’article suivant se concentre sur les listes déroulantes créées à partir de la fonctionnalité de validation de données et indique comment procéder lorsque les données servant à créer la liste sont dans la même feuille que la liste elle-même, lorsqu’elles ne sont pas dans la même feuille, lorsqu’elles sont mises sous forme de tableau et lorsqu’elles ne le sont pas.

 

1 – Liste de validation de données dans la même feuille que les données sources

Cette section traite de la création de listes de données via la fonctionnalité de validation de données pour des listes dont les données sources appartiennent à la même feuille.

 

1.1 Les données sources ne sont pas mises sous forme de tableau

Les premiers exemples sont créés à partir de données sources qui ne sont pas mises sous forme de tableau. Pour savoir ce qui signifie mettre les données sous forme de tableau, vous pouvez relire l’article suivant: Découvrez la magie des tableaux Excel.

Validation de données sans tableau

 

D’abord, vous pouvez créer votre liste en sélectionnant les cellules, B6 à B9, tel qu’illustré ci-dessous:

Liste ne s'allonge pas - sélection cellules

Le problème avec cette solution toutefois, c’est que lorsque vous devrez ajouter un produit supplémentaire dans vos données sources (supposons le Produit E), votre liste de validation n’en tiendra pas compte. Elle ne s’allongera pas en fonction des nouveaux items ajoutés à la source.

 

Une autre option est de créer un champ nommé pour les cellules B6 à B9 et de référer au champ nommé dans votre menu de validation de données.

Liste ne s'allonge pas - champ nommé sans formule

 

Ci-dessous, vous pouvez constater que le champ nommé ProduitsSansTableau réfère aux cellules B6 à B9 et ce faisant, là aussi, si vous ajoutez le Produit E à vos données sources, celui-ci ne sera pas considéré dans votre liste déroulante.

Liste ne s'allonge pas - champ nommé sans formule 2

Pour en savoir davantage sur les champs nommés, vous pouvez relire l’article Astuce Excel: Nommer une plage de cellules.

 

Finalement, vous pouvez utiliser une fonction Decaler/Offset pour créer une plage de données dynamique. Pour revoir comment utiliser la fonction Decaler/Offset afin de créer une plage de données dynamique, vous pouvez relire l’article suivant: Excel: Fonction Decaler (Offset).

Liste s'allonge avec formule

 

Ce faisant, lorsque vous ajouterez le Produit E à votre source de données, celui-ci apparaîtra dans votre liste déroulante (voir image suivante):

Liste s'allonge avec formule 2

 

1.2 Les données sources sont mises sous forme de tableau

L’exemple suivant a été créé à partir de données sources qui sont mises sous forme de tableau.

Liste avec tableau même page

 

Si vos données sources sont dans la même feuille que votre liste de validation, lorsque vous créerez votre liste de validation, vous verrez des références aux cellules des items sources, dans notre exemple, une référence aux cellules B6 à B9. Toutefois, lorsque vous ajouterez un nouvel item dans vos données sources, la référence sera automatiquement modifiée pour aller chercher le nouvel item dans la cellule B10. Ainsi, votre liste déroulante sera toujours mise à jour correctement. Pour cette raison, il est largement préférable de créer vos données sources sous forme de tableau.

Liste s'allonge toujours

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

 

2 – Liste de validation de données dans une autre feuille que les données sources

Cette section traite de la création de listes de données via la fonctionnalité de validation de données pour des listes dont les données sources proviennent d’une autre feuille.

 

2.1 Les données sources ne sont pas mises sous forme de tableau

D’abord, vous pouvez créer votre liste en sélectionnant les cellules de la source de données mais comme c’était le cas lorsque les données sources résidaient dans la même feuille que la liste, celle-ci ne s’allongera pas lorsque de nouvelles données seront ajoutées à la source.

Tableau Liste ne s'allonge pas avec sélection cellules différente feuille

 

Ensuite, vous pouvez également référer à une plage de champ nommé mais vous rencontrez la même limite. La liste ne s’allongera pas en fonction des nouveaux items.

Liste ne s'allonge pas différente feuille champ nommé sans formule

 

Pour voir la liste s’allonger en fonction des nouveaux items, vous devrez utiliser la fonctoin DECALER/OFFSET, comme c’était le cas lorsque les données non mises sous forme de tableau étaient dans la même feuille que la liste.

Liste s'allonge différente feuille avec formule

 

2.2 Les données sources sont mises sous forme de tableau

Si vos données sources sont mises sous forme de tableau et que vous créez une liste de validation dans un autre feuille, vous rencontrerez les mêmes limitations qu’en 2.1 si vous référez à une sélection de cellules, comme B6 à B9. Comme en 2.1 également, vous pourrez créer une fonction DECALER/OFFSET pour vous assurer que votre liste s’allongera en fonction des nouveaux items ajoutés dans vos données sources. Toutefois, il y a une façon beaucoup plus simple et directe à utiliser lorsque vos données sources sont mises sous forme de tableau et que votre liste est créée dans une autre feuille. Il s’agit de créer un champ nommé à partir des cellules B6 à B9 et de faire pointer la validation de données sur ce champ nommé, tel qu’illustré ci-bas:

Tableau liste s'allonge avec champ nommé différente feuille

 

Le champ nommé LesProduits réfère aux produits du tableau. Lorsqu’un nouveau produit est ajouté, le champ nommé l’inclut. C’est ce qui permet de le voir apparaître dans la liste de validation.

Tableau liste s'allonge feuille diff champ nommé

 

Une autre option est d’utiliser une fonction INDIRECT. Dans l’exemple ci-dessous, la fonction INDIRECT est utilisée sur la tableau de données Tableau3, sur la colonne de données Produits. Notez bien l’usage des guillemets et des crochets. Pour un rappel de l’usage de la fonction INDIRECT, vous pouvez relire l’article Excel: La fonction Indirect pour créer des sommaires exécutifs en un clic de souris.

Liste validation avec Indirect
 


 

Fichier d’accompagnement

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formations complémentaires

Si les validations de données et les champs nommés vous intéressent, jetez un coup d’oeil à notre formation Excel – Mise à niveau.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

François Le May
Écrit il y a 7 mois
Il était grandement temps que je la suive!

Mon efficacité au travail et ma capacité à faire des analyses rapidement vont être grandement améliorées.

Mélanie Bonneau
Écrit il y a 7 mois
Adaptée pour le monde des CPA

Je trouve que cette formation est la plus adaptée pour le monde des CPA. En d'autres mots, j'apprends davantage ici que dans une formation générale de type "débutant - intermédaire - avancé" et je trouve que c'est plus concret pour mes activités quotidiennes. Merci!

Isabelle Madé
Écrit il y a 9 mois
Excellente formation!

Le découpage des sections est parfait, on peut prendre le tout à petites bouchées à notre rythme. J'utilise Excel depuis très longtemps et comme bien des gens j'ai appris sur le tas. Cette mise à niveau m'a permis d'apprendre ou mieux comprendre certaines formules et fonctionnalités que j'aurais dû maîtriser bien avant! Je suis prête pour la suite...

Melissa Forgues
Écrit il y a 10 mois
J'ai adoré!

Je croyais faire cette formation pour rien puisque je travaille avec Excel et prit quelques cours dans le passé. Je croyais que je maîtrisais toute la base! Ayayaye! Je me suis trompé! La formatrice donne énormément de trucs et des informations jusque là inconnue pour moi, jamais montré en salle de formation avec d'Autres compagnies... Déjà là, je vais sauvé beaucoup de temps. OMG! Bravo!

Sylvie Giroux
Écrit il y a 2 ans
Captivant

La formatrice expliquait très clairement chaque exemple et avait un ton de voix dynamique. En suivant une formation en ligne, je craignais de m'endormir, mais non! J'ai été captivée et intéressée tout au long de ma formation.

 

Si la fonction DECALER/OFFSET vous intéresse, jetez un coup d’oeil à notre formation Excel – Modélisation financière (niveau 1).

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Mathieu Poliquin
Écrit il y a 2 mois
Bonnes pratiques et des méthodes qui me permettront de voir des gains de performance

Super pertinent. Malgré que je suis déjà de l'expérience en modélisation financière dans Excel, j'arrive tout de même à prendre des bonnes pratiques et des méthodes qui me permettront de voir des gains de performance concret et applicabe rapidement. Merci beaucoup

Jean-Philippe Provost
Écrit il y a 3 mois
Super formation !

Me permettra sans aucun doute de relevé la qualité de mes modélisations financières même si je modélise depuis plus de 10 ans.

Antoine Champagne
Écrit il y a 8 mois
Excellents outils

Des outils excellents pour bien organiser et rendre clair nos fichiers Excel. Je travaille à tous les jours avec excel et c'est bon de pouvoir bonifier et améliorer mon travail. Vraiment, Mme Marchand, vous êtes excellente; j'ai hâte à la formation suivante. Ça fait plaisir d'encourager une entreprise québécoise qui "excel" dans son domaine.

Mélanie Hébert
Écrit il y a 2 ans
Très utile

J'aime que nous avons des excel que nous pouvons revoir par la suite j'aime que la formation soit faite avec des sections définies et claires et que chaque sujet ait son propre document de présentation ce qui permet de le consulter facilement au besoin

 

29 réflexions sur “Excel: Créer une liste de validation dynamique avec les données d’une autre feuille”

  1. Bonjour,
    Bravo comme toujoursd, Très bon tuto mais … une conjugaison qui semble être erronée.
    “Si vos données sources sont dans la même feuille que votre liste de validation, lorsque vous créerez (créerai) votre liste de validation, vous verrez des références…”
    Bien cordialement
    G.D

  2. Bonjour Sophie, comme toujours une excellente source d’inspiration. Cependant, je crois qu’il y a une coquille dans le titre de votre point 2.2,qui traite du cas où les données sources sont sous forme de tableau. Bien cordialement.

  3. Michel Goulet formation sur mesure

    Bonjour Sophie
    Bravo pour ta présentation.
    J’ai utilisé la stratégie de validation des données, pour m’assurer que l’utilisateur de ma feuille (liste des membres d’une association) saisisse, par exemple les noms exactes d’une province, d’une ville, du secteur ou de l’arrondissement, de la rue, etc.
    J’avais utilisé des champs nommés sur une seule et même feuille intitulé « Champs de validation ». Toutefois j’ai réalisé que si les champs nommés n’étaient pas placés en escalier cela créait des conflits entre les champs nommés, lors de l’insertion (ajout) de nom de ville ou autres noms.
    Je crois que la stratégie de la création d’une feuille distincte pour chaque type de validation est plus simple, mais augmente de façon importante la taille du fichier Excel.

      1. Vincent BOURGEOIS

        Bonjour Sophie,

        J’ai bien suivi votre lien. Je cherche à importer des listes depuis un autre fichier excel (fermé) grace à une macro mais ne trouve pas comment faire. J’ai bien pensé à créer une macro qui copie/colle la feuille du classeur contenant ces listes, puis recrée ces mêmes listes mais j’imagine qu’il doit y avoir plus simple. auriez-vous un cours/tuto/conseil?

        Cordialement

      2. Vincent BOURGEOIS

        Bonjour,

        J’ai bien suivi votre lien. Je cherche à importer des listes depuis un autre fichier excel (fermé) via une macro mais ne trouve pas la solution. Aucun bout de code sur le net, et les fonction copier/coller ne marchent pas pour ce genre d’objet. Auriez vous une solution?
        J’ai bien pensé importer seulement les données, puis que la macro recrée les listes mais c’est faire bien compliqué pour une chose “toute simple”.
        Cordialement

        1. Bonjour Vincent,

          Le plus facile et le plus optimal est sans aucun doute via Power Query. J’ai un article qui crée des listes dans Excel à partir d’une source de données externe: https://www.lecfomasque.com/excel-creer-des-listes-deroulantes-dynamiques-et-sans-doublons/. Dans cet article, on doit créer les listes à partir de la base de données. Si vos listes sont déjà créées dans l’autre fichier Excel, c’est encore plus simple…

          Au plaisir,

          Sophie

          1. Vincent BOURGEOIS

            Bonjour Sophie,

            Malheuresement cet Add-in n’est pas disponible dans le menu file>option>add-ins>COM add-ins sur le poste sur lequel je travaille… (W10 et excel 2016 pourtant!) De plus comme le fichier sera par la suite utilisé par beaucoup de collègues il me semblait plus simple que ce soit une macro qui se lance pour importer ses listes, qu’ils n’aient besoin de rien d’autre que de cliquer sur un bouton.

            Bien cordialement,

            Vincent

  4. Bonjour, Pour aller plus loin, si je reprends votre exemple, je souhaiterai proposer dans mon tableau une liste de choix selon un référentiel d’une autre page. Imaginons que mes produits soient associés à des références. Pour un produit j’ai plusieurs références. je dispose d’un coté mes produits et références d’un côté et mon listing de commandes de l’autre coté. je sais ce qui a été commandé ie quel produit a été mouvementé et j’ignore pour quelle référence. je souhaiterai donc que les références apparaissent dans ma liste déroulante mais selon le produit mouvementé de la ligne de commande. j’ai essayé d’insérer une formule (recherchev) dans la liste de choix… Merci pour votre aide

    1. Bonjour,

      Je ne suis pas certaine de comprendre votre question mais si je la comprends bien, vous cherchez à créer des listes déroulantes dépendantes.

      Le cas échéant, vous pourriez utiliser la technique suivante: https://www.lecfomasque.com/excel-listes-deroulantes-dependantes/.

      Vous pourriez aussi créer une première liste avec une validation de données et une deuxième liste (la liste dépendante) avec une validation de données comprenant une formule qui rapporte une plage de données (via une fonction index).

      Si ce n’est pas ce que vous cherchez comme réponse ou si vous ne comprenez pas bien mes réponses, veuillez svp poser votre question dans notre forum et y joindre votre fichier afin que nous puissions y insérer les formules nécessaires.

      Forum: https://www.lecfomasque.com/forum/.

      Merci,

      Sophie

  5. Bonjour,
    Dans mon fichier j’utilise une liste de validation des données qui est issue d’un tableau (ça me permet de mettre à jour la liste simplement sans formule).
    Par contre mon fichier évolue et maintenant pour compliquer le tout mon j’ai besoin d’avoir des listes déroulantes indépendantes sans doublons et qui s’alimentent toutes seules en fonction de l’évolution de ma base de donnée. (je ne sais pas si je suis clair).
    En gros dans ma base de donnée qui évolue et se modifie régulièrement j’ai:
    A blanc
    A vert
    B blanc
    B noir
    C bleu
    Je voudrais choisir dans une cellule le type “A” par exemple (une liste qui me propose A B ou C)
    Puis dans une autre cellule la couleur (comme j’ai choisi A je dois avoir le choix entre blanc ou vert)
    … et ainsi de suite pour à la fin ressortir le prix du type A de couleur blanc par exemple (prix qui est dans ma base de donnée bien sur).
    Thomas

  6. Bonjour,
    Peut-on créer une liste déroulante dynamique dont les valeurs proviennent des résultats d’un tableau croisé dynamique ?
    Merci,
    Eric

      1. Bonjour,

        Je suis dans ce cas.
        J’ai un tableau de données regroupant des noms et des qualités associés aux noms (A, B, C …).
        Mon tableau dynamique croisé permet de récupérer la liste des noms ayant la qualité B.
        Cela me permet ensuite dans un autre tableau de gestion de charge de mettre un liste déroulante des noms récupérés dans la colonne “Nom métier B”.
        Les formules avec la syntaxe tableau ne fonctionne pas. Je trouve dommage d’utiliser la syntaxe DECALER.

        Merci pour vos tutos et d’avance pour votre réponse.

        Eric

        1. Bonjour,

          Je vous déconseille fortement de créer une liste à partir des champs d’un tableau croisé dynamique. Ce serait aller à l’envers des bonnes pratiques dans Excel. Le tableau croisé dynamique est par définition, dynamique, et un usager peut en changer la forme et la contenu aisément, ce qui peut avoir un impact significatif sur vos listes créées à partir de ce tableau croisé dynamique. Le tableau croisé est généralement un outil de présentation ou une interface intermédiaire entre une base de données et un graphique.

          Alors, je vous propose d’utiliser la technique décrite dans cet article ou alors, si vous utilisez une version Excel qui a déjà accès aux fonctions matricielles dynamiques, je vous conseille la fonction UNIQUE, à utiliser non pas sur votre tableau croisé dynamique mais plutôt sur la base de données qui a servi à alimenter votre tableau croisé dynamique.

          Voici un lien vers une vidéo qui présente les fonctions matricielles dynamiques https://www.lecfomasque.com/excel-decouvrez-les-dernieres-nouveautes/.

          Au plaisir,

          Sophie

  7. Bonjour,

    Super Tuto surtout sur la partie tableau.
    Je souhaitais savoir comment je pouvais activer la saisie automatique dans une liste déroulante sur la base d’un objet tableau. J’ai essayer avec Decaler et NBVAL et rien n’y fait, cela ne veut pas fonctionner.
    Merci pour votre aide.

    Patrice.

    1. Bonjour Patrice,

      Je ne suis pas certaine de comprendre votre question… Normalement, la saisie automatique va fonctionner seulement lorsque la valeur de la liste aura été inscrite au moins une fois dans une des lignes du tableau. Tant qu’elle n’aura pas été inscrite dans une des lignes du tableau, elle ne s’activera pas.

      J’espère avoir bien compris votre question.

      Sophie

  8. Bonjour Sophie, Merci pour cette Présentation.
    J’ai une question en Excel 2016, quand je fais une validation de données d’une cellule ex: B2, mais quand je fais copier coller une valeur non valide d’une autre cellule ex: D5 dans B2, la validation s’écrase.
    Comment je pourrais sans utilisation de VBA soit :
    1- Interdire à entrer des valeurs invalides même par copier/coller.
    2- empêcher Copier/coller dans cette feuille.

    Merci d’avance,

  9. Bonjour,
    dans votre exemple si je modifie le nom du produit E dans la colonne B alors que je pointe ce même produit dans la cellule ou se trouve la liste de valeur en colonne E, je souhaiterai qu’il soit modifié en même temps et que je ne sois pas obligé de le re sélectionner pour qu’il soit bien pointé.
    En le renommant je perd le focus sur ce produit alors que je le pointais…
    Avez vous une solution pour moi ?
    En vous remerciant par avance.

    1. Bonjour,

      Si vous renommez le produit E, vous allez voir le nouveau nom de produit E dans la liste de validation automatiquement. Par contre, la valeur qui existe déjà dans une cellule ne se modifiera pas d’elle même… il s’agit d’une entrée de données. Vous pourriez minimalement faire un Chercher/Remplacer.

      Au plaisir,

      Sophie

  10. Bonjour Sophie, merci pour ce super tuto. J’ai appliqué la solution dynamique avec formule =DECALER mais ma liste déroulante est vide. J’ai une feuille “Commandes” et une feuille “Clients”. Je souhaite que la liste affiche les données de la feuille “Clients” qui sont dans la colonne A. Ma formule est donc la suivante : =DECALER(Clients!$A$2;1;0;NBVAL(Clients!$A:$A)-1;1). Si j’intègre cette formule dans une cellule de la feuille “Commandes”, ça marche. Par contre dès que je mets cette formule dans Format de contrôle > Plage d’entrées, puis que je clique OK pour valider, ma liste est vide et si j’ouvre à nouveau Format de contrôle > Plage d’entrées, le champ est vide (n’a pas gardé la formule précédente). Pourriez-vous m’aider à résoudre ce problème ? D’avance merci beaucoup pour votre aide.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top