Forum Discussion
Create a dynamic drop-down menu without displaying previous data with Excel online
Hello everyone,
I would like to create a dynamic drop-down menu without displaying previous data. I have a list of first names that I would like to be able to add or reduce as I wish and ensure that each time I select a first name on a line, it disappears from my list on the next line, I have my list of first names on another sheet (to make it cleaner).
I tried several tutorials or videos but I can't find what I want. I should point out that I use the Microsoft 365 suite online and that I am completely new to Excel.
Thank you in advance for your help
Florent_Z Perhaps the working example of such a drop-down list in the attached file will help you understand the tutorials you find on-line.
You can create an auxiliary list of "remaining names" with filter and countif formula.
- Suppose you have your list of names as a table format (E13:E17 on my example).
- You have to assign a unique name to a unique question/task/condition (E4:E7 on my example).
- You create a list of remaining names (G14:G18 on my example).
On G14, you would write the following formula:
=FILTER(TABLE1[List of names],COUNTIF(TABLE2[Name],TABLE1[List of names])<1,"There are no names remaining")It will automatically show in your newly created list of remaining names the proper.
Additionally, you should create the data validation properly. Select cells E4:E7, go to data, data validation, select the form of a list and then use as a source "=G14#". It is important that you use the #, as it will take into account that it is an array.
Then, as you select each name to each task or whatever, it will disappear automatically from the remaining list of names. This has been created to dynamically adjust when you add or substract rows in the source list of names.
Hope it works well for you!
- Thank you all for your constructive responses, despite my inexperience with Excel, I managed to use the two solutions you proposed to me. Thank you so much Florent
