Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

Comment cacher les éléments précédemment utilisés dans la liste déroulante?

Dans Excel, vous pouvez créer rapidement une liste déroulante normale, mais avez-vous déjà essayé de créer une liste déroulante lorsque vous choisissez un élément, le précédent sera supprimé de la liste? Par exemple, si j'ai une liste déroulante avec des noms 100, comme je sélectionne un nom, je veux supprimer ce nom de la liste déroulante, et maintenant la liste déroulante contient des noms 99, et ainsi de suite jusqu'à ce que la liste déroulante soit vide. Peut-être, c'est un peu difficile pour la plupart d'entre nous, et ici, je peux parler de la façon de créer une telle liste déroulante dans Excel.

Masquer les éléments précédemment utilisés dans la liste déroulante avec des colonnes auxiliaires

Onglet Office Activer l'édition et la navigation par onglets dans Office, et simplifiez grandement votre travail ...
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%
  • Réutiliser n'importe quoi: Ajoutez les formules, graphiques et autres éléments les plus utilisés ou les plus complexes à vos favoris et réutilisez-les rapidement.
  • Plus que le texte 20 comprend: Extraire le numéro de la chaîne de texte; Extraire ou supprimer une partie des textes; Convertissez les nombres et les devises en mots anglais.
  • Fusionner les outils: Plusieurs classeurs et feuilles en un; Fusionner plusieurs cellules / lignes / colonnes sans perdre de données; Fusionner les lignes en double et la somme.
  • Outils Split: Fractionner les données en plusieurs feuilles en fonction de la valeur; Un classeur pour plusieurs fichiers Excel, PDF ou CSV; Une colonne à plusieurs colonnes.
  • Coller Sauter Lignes cachées / filtrées; Compte et somme par couleur de fond; Envoyez des e-mails personnalisés à plusieurs destinataires en bloc.
  • Super filtre: Créez des schémas de filtrage avancés et appliquez-les à toutes les feuilles. Trier par semaine, jour, fréquence et plus; Filtre en gras, formules, commentaires ...
  • Plus que de puissantes fonctionnalités 300; Fonctionne avec Office 2007-2019 et 365; Prend en charge toutes les langues; Déploiement facile dans votre entreprise ou organisation.

flèche bleue droite bulle Masquer les éléments précédemment utilisés dans la liste déroulante avec des colonnes auxiliaires


Supposons que vous ayez une liste de noms dans la Colonne A comme la capture d'écran suivante, puis suivez les étapes ci-dessous une par une pour terminer cette tâche.

doc-hide-used-items-liste déroulante-1

1. En plus de votre liste de noms, veuillez entrer cette formule = SI (NOMBRE ($ F $ 1: $ F $ 11, A1)> = 1, "", ROW ()) dans la cellule B1, voir la capture d'écran:

doc-hide-used-items-liste déroulante-1

Note: Dans la formule ci-dessus, F1: F11est la plage de cellules que vous souhaitez placer dans la liste déroulante A1 est votre cellule de nom.

2. Ensuite, faites glisser la poignée de remplissage sur la plage qui contient cette formule, et vous obtiendrez le résultat suivant:

doc-hide-used-items-liste déroulante-1

3. Et continuez à appliquer une formule dans la colonne C, veuillez taper cette formule: =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1)))) dans la cellule C1, voir la capture d'écran:

doc-hide-used-items-liste déroulante-1

4. Remplissez ensuite cette formule jusqu'à la plage dont vous avez besoin, voir capture d'écran:

doc-hide-used-items-liste déroulante-1

5. Vous devez maintenant définir un nom de plage pour ces noms dans la colonne C, sélectionnez C1: C11 (la plage à laquelle vous appliquez la formule à l'étape 4), puis cliquez sur Formules > Définir le nom, voir capture d'écran:

doc-hide-used-items-liste déroulante-1

6. Dans le Nouveau nom boîte de dialogue, tapez un nom dans la zone de texte Nom, puis entrez cette formule =OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1) dans le Se réfère à champ, voir capture d'écran:

doc-hide-used-items-liste déroulante-1

Note: Dans la formule ci-dessus, C1: C11 est la plage de colonnes d'assistance que vous avez créée à l'étape 3 et la feuille 2 est la feuille actuelle que vous utilisez.

7. Après avoir terminé les réglages, vous pouvez créer une liste déroulante, sélectionnez la cellule F1: F11 où vous souhaitez placer la liste déroulante, puis cliquez sur Data > Validation des données > Validation des données, voir capture d'écran:

doc-hide-used-items-liste déroulante-1

8. Dans le Validation des données boîte de dialogue, cliquez sur Réglages onglet, puis choisissez liste du Permettre liste déroulante, puis sous Source section, entrez cette formule: = namecheck, (namecheck est le nom de la plage que vous avez créée à l'étape 6), voir capture d'écran:

doc-hide-used-items-liste déroulante-1

9. Et puis cliquez OK pour fermer cette boîte de dialogue, la liste déroulante a été créée dans la plage sélectionnée, et après avoir sélectionné un nom dans la liste déroulante, ce nom utilisé sera supprimé de la liste et n'affiche que les noms qui n'ont pas été utilisés , voir capture d'écran:

doc-hide-used-items-liste déroulante-1

Pointe: Vous ne pouvez pas supprimer les colonnes d'aide que vous avez faites au-dessus, si vous les supprimez, la liste déroulante ne sera pas valide.


Articles Liés:

Comment insérer une liste déroulante dans Excel?

Comment créer rapidement une liste déroulante dynamique dans Excel?

Comment créer une liste déroulante avec des images dans Excel?


Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe Créer une liste de diffusion et envoyer des emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit le nombre de clics de souris pour vous chaque jour!
fond officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    John · 1 years ago
    Is there a way to have only some of the options get removed when selected and others be permanent?
  • To post as a guest, your comment is unpublished.
    Keith Race · 1 years ago
    How do I get this activity to work if I transpose from Row to Column
  • To post as a guest, your comment is unpublished.
    Sam · 1 years ago
    I’ve entered all the formulas correctly, but the only name showing up is the first one on the list. What am I doing wrong??
  • To post as a guest, your comment is unpublished.
    Stefan · 2 years ago
    Works great, however, if you have two people on the list with the same name e.g. John Smith it removes both incidents of 'John Smith' from the list when you select one of them.


    Is there a way to amend this so that you have have multiple versions of the one name without them all being removed?


    Thanks.
  • To post as a guest, your comment is unpublished.
    Derric · 3 years ago
    How would you change this formula for use of data validation list across multiple rows instead of a single column. Is that possible?

    Thanks
  • To post as a guest, your comment is unpublished.
    Mohanraj · 3 years ago
    Awesome, Working for me...
  • To post as a guest, your comment is unpublished.
    tim · 3 years ago
    I changed mine to accommodate my needs "=IF(COUNTIF(Statusboard!$C:$C,A1)>=1,"",ROW())" Make sure you change the $F$1:$F$11 from "=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())" to wherever your list is in my case it was Statusboard!$C:$C,A1.
  • To post as a guest, your comment is unpublished.
    Yolanda · 4 years ago
    I need to be able to create a list that has items that disappear but I want to be able to use it in multiple columns on the same sheet. Does anyone know how to do that? - Thanks!
  • To post as a guest, your comment is unpublished.
    Yolanda · 4 years ago
    I need to be able to use this list multiple times in multiple columns but as soon as an item disappears it's gone for good. Does anyone know how I can create this and be able to use it for multiple columns? Thanks!
  • To post as a guest, your comment is unpublished.
    NighT · 4 years ago
    Hey all,
    I got this to work, and it works like a charm!
    @Amanda, yes. I have my data on a different sheet as the dropdown menu. See the below written formulas.

    @Filip,
    Yes, you can use a formula to automatically select the unique values from a list. I used this to have a dynamic list.

    Mind you; I used google and a lot of different website to get to this formula, so it's not all my own work.
    First: to get the list of things to display:
    IF(INDEX(Sheet1!$A$2:$A$100;MATCH(0;COUNTIF($AA$14:AA14;Sheet1!$A$2:$A$100);0))=0;"";INDEX(Sheet1!$A$2:$A$100;MATCH(0;COUNTIF($AA$14:AA14;Sheet1!$A$2:$A$100);0)))

    === Basically this is the same formula twice. Which will give an empty ("") value if no further unique values are found. Anyway, the formula returns an unique list of values from my 'Sheet1!'. (lets say for easy reference I have this formula on Sheet 2, column A)

    Then I just start using the same formula as above (my sheet 2 column B):
    IF(COUNTIF(Sheet3!$S$2:$U$4;A1)>=1;"";ROW())

    === Sheet 3 is where I have my dropdowns. This is probably what you're looking for Amanda.

    Then the last bit of the formula:
    IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$24);"";INDEX(A:A;SMALL(B$1:B$24;1+ROW(A1)-ROW(A$1))))

    === This formula is in my Sheet 2 column C.
    This *should* work.

    Good luck to you all! And again, a great thank you to the author!
    Regards,

    NighT
  • To post as a guest, your comment is unpublished.
    Filip · 4 years ago
    Thanks for this guide. I'm just asking if it's possible when I update a value in formula, this value will be updated automatically in list of items?

    Example:
    I select value "James" from list in cell F
    Now, I want to change value from "James" to "Thomas". I rewrite value "James" to "Thomas" in cell A, formula automatically change value in cell C. It's OK, but I need this changed value is automatically updated in cell F as well.

    How can I reach that? Any ideas?
  • To post as a guest, your comment is unpublished.
    Amanda · 4 years ago
    Does anyone know if it is possible to use this between sheets? For instance if the original info (the column A portion) is on one sheet, but the dropdown (the column F portion) is on another? How would that change the formula?
  • To post as a guest, your comment is unpublished.
    Kent · 4 years ago
    Hi, I can't get it to work properly.
    When trying to complete step 8 I get a message about 'The source returns an error at evaluation. Do you wish to continue?'

    I am using Excel 2010, any idea?
    • To post as a guest, your comment is unpublished.
      Amanda · 4 years ago
      I had that happen at first, too. I had not changed the "sheet2" portion to the proper sheet name for what I was using.