Create an Excel Dynamic Drop-Down List That Removes Selected Names
Question details
Create a dynamic Excel data-validation drop-down that shows only names that have not already been selected in previous rows.
- Product
- Excel for the web
- Device & OS
- not provided
- Scenario
- The source names and the rows where users make selections are stored in Excel tables. The user wants each drop-down to update as names are selected or new source names are added.
- Observed behavior
- Selected names should disappear from the available drop-down choices, while unselected names remain available through a spilled helper list.
Store the master names and the selected-name entries in Excel tables so their ranges expand automatically. Make sure the helper-cell area is empty, because the FILTER formula needs room to spill its results.
Use FILTER and COUNTIF to create an available-names helper list
Use this method when you need a data-validation list that automatically excludes names already selected in another table.
The FILTER formula returns source names whose count in the selected-name table is less than one. Point data validation to the formula's spilled range so the choices refresh automatically.
Create a table for all available names, such as Table1 with a column named List of names. Create a second table for the rows where users select names, such as Table2 with a column named Name.
Click an empty helper cell, such as G14, and enter =FILTER(Table1[List of names],COUNTIF(Table2[Name],Table1[List of names])<1,"There are no names remaining"). Press Enter to create the spilled list.
Select the cells or table column where users choose names, such as the Name cells in Table2.
Open Data > Data Validation, choose List, and enter =G14# in the Source box. The # character includes every current result from the spilled list.
Choose a name in one of the validated cells, then open another drop-down. The selected name should no longer appear; add a new name to Table1 to make it available automatically.
Build a filtered, duplicate-free name drop-down in WPS Spreadsheets
WPS Spreadsheets lets you organize source names in tables, calculate available names with FILTER and COUNTIF, and use the resulting range for a dynamic drop-down. Its familiar spreadsheet interface makes it easy to move an existing Excel workflow to WPS Office.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing your source-name and selection tables.
- 2. Add the available-names formula: Click an unused helper cell and enter =FILTER(Table1[List of names],COUNTIF(Table2[Name],Table1[List of names])<1,"There are no names remaining").
- 3. Select the cells that need choices: Highlight the Name cells where users should select an unused person or item.
- 4. Create the validation list: Choose Data > Data Validation, select List, and set the source to the helper range or its spilled-range reference, such as =G14#.
- 5. Verify the list updates: Select a name in one row and open the list in another row to confirm that previously selected names are removed.
Frequently Asked Questions
Why does the data-validation source use G14# instead of G14?
The # symbol refers to the entire spilled result beginning in G14. Without #, the drop-down would use only the single helper cell rather than every available name.
What happens when all names have already been selected?
The FILTER formula displays the text "There are no names remaining" because it is supplied as the third FILTER argument. You can replace that text with another message if needed.
Will newly added source names appear in the drop-down automatically?
Yes. If the source names are in an Excel table such as Table1[List of names], adding a new table row expands the reference and allows FILTER to include the new unselected name.
Can I prevent the same name from being selected twice?
Yes. Use the helper formula =FILTER(Table1[List of names],COUNTIF(Table2[Name],Table1[List of names])<1,"There are no names remaining") and set each selection cell's Data Validation list source to the spilled range, such as =G14#.
