Drop Down from Filtered List

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.

Drop Down from Filtered List

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.

Employee Drop Down

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.

Filtered List

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.

Number the List

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

=--SUBTOTAL(3,B$3:B3)

• The function has COUNTA (3) as the first argument
• The range starts at cell B$3 (locked row), and goes down to the current row, B3 (not locked)
• The two minus signs at the start of the formula will prevent problems when filtering the table. Without them, Excel always leaves the last row visible.

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

Make a New List

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.

=IFERROR(INDEX(tblEmp[Employees], MATCH(B2,tblEmp[ListNum],0)),"")

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.

Dynamic List of Names

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:

=MAX(tblEmp[ListNum])

That returns the highest number from the main list.

Create a Dynamic Named Range

Next, to create a dynamic named range, that will adjust its number of rows based on the count of visible names, follow these steps:

• On the Ribbon's Formulas tab, click Define Name
• Type the name: EmpListAvail
• For the Scope, select Workbook
• In the Refers to box, enter this formula:
• =ListDV!$C$2:INDEX(tblEmpAvail[Emp], ListDV!$F$1)
• Click OK

The formula for the dynamic range starts at cell C2 on the ListDV sheet.

• =ListDV!$C$2:

Then, the INDEX function returns the end cell in the Emp column, based on the number in cell F1

• INDEX(tblEmpAvail[Emp], ListDV!$F$1)

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:

• On the Ribbon's Formulas tab, click Define Name
• Click on the new name: EmpListAvail
• Click in the Refers to box, and the range will be highlighted on the worksheet, with "marching ants".
• Click Close.

Change the Drop Down Lists

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.

• On the Schedule sheet, select all the cells that have the drop down list for employee names
• On the Data tab of the Ribbon, click Data Validation
• In the Source box, refer to the dynamic named range: =EmpListAvail
• Click OK

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 Sample File

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.

More Tutorials

Data Validation Basics

Create Simple Dependent Lists

Flexible Dependent Drop Downs