With Excel data validation, you can create drop down lists, to make data entry easier. In this example, some employees will be on vacation, and their names will be omitted from the drop down, when the employee list is filtered.
See how to make an Excel drop down list show only the visible items from a filtered list, in this step-by-step video. There are written instructions below the video, and you can download the completed sample file.
In this example, the workbook has a list of employees, in a formatted Excel table (tblEmp).
There is a named range – EmpList – based on the Employees column in that table.
In the sample workbook, there is a schedule sheet, with a data validation drop down list based on the EmpList named range. You can click an arrow in any cell in the Staff column, and select any of the employee names.
In the Employee table, there is a column to mark an X, for anyone who's on vacation. To see who's available for work, you filter the list, and hide all the rows that contain an X in the Vacation column
Filtering the Employee list does not affect the data validation drop down list of employees on the Schedule sheet. The drop downs continue to show all the employee names.
To create an employee list that only includes the visible rows, we'll clear the filter, and then add a new column (ListNum) to the Employee table, to number the visible rows.
The new column has the following formula, using the SUBTOTAL function. SUBTOTAL ignores rows that are hidden by a filter, so only the visible rows will be numbered
Here is the table with all rows visible, showing the SUBTOTAL formula results in each row.
To show how the numbering formula works, there are cells at the top that link to the first 2 rows, so we can check the results, after some of the rows are hidden.
NOTE: Those formulas are for demo only, they are not needed in the final workbook
To test the SUBTOTAL formula, filter the Employees table, to hide the rows with an X in the Vacation column. The numbers change, and the visible rows are numbered sequentially.
The SUBTOTAL function ignores rows that have been hidden by a filter, so it will only calculate a count on visible rows. The formulas at the top show that there is a 0 result for the SUBTOTAL function in the hidden row for D3
Now that the full list is numbered, we'll create a second list of employees. The new list will have formulas, to get the names, based on the numbers in each row.
The new list has the headings, ID and Emp, and numbers are typed in the ID column. Be sure to add at least as many numbers as there are employees in the main list – I added a few extra numbers too.
In the Emp column add this formula, to return the employee name, based on the ID numbers.
The INDEX function returns a name from the Employees column, for the row that has the same number as the ID.
This screen shot shows the new list, with the numbered names from the filtered main list.
We want to use this short list for our drop down list, so we'll create a dynamic named range with the names in our new list.
To make the named range the correct size, a formula on the worksheet get the number of visible names.
To get the count, the cell contains this MAX formula:
That returns the highest number from the main list.
Next, to create a dynamic named range, that will adjust its number of rows based on the count of visible names, follow these steps:
The formula for the dynamic range starts at cell C2 on the ListDV sheet.
Then, the INDEX function returns the end cell in the Emp column, based on the number in cell F1
If that number changes, the end cell will adjust to the the new number.
In the Name Manager, to check the Dynamic Range, follow these steps:
The final step is to change the drop down lists, so they are based on the dynamic list of employees, instead of the full list of names.
Now, test the drop down list, and it should show the short list of available employees, instead of the full list.
NOTE: If names were previously selected from the full list, and are not in the filtered list, you might see Data Validation error warnings.
Get the completed workbook for the Excel Drop Down from Filtered List tutorial. The zipped file is in xlsx format, and does not contain macros.
Last updated: July 12, 2021 11:24 AM