Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table.
The animated image below demonstrates how values (First Name) in a drop down list changes based on how the table is filtered (Country).
How to build this
First we need to insert a new worksheet, this new worksheet will contain a formula that extracts filtered values from the Excel defined Table.
The dropdown list contains a named range that will fetch the extracted values from the new worksheet.
Calculation worksheet

Right click on any worksheet tab at the very bottom of the Excel screen.

Click on "Insert" to open a dialog box.
 Select "Worksheet" icon.
 Click OK button.
To rename the worksheet simply right click on the worksheet tab and then click on "Rename".
Type a new worksheet name and press Enter, I named it "Calculations".
Formula
I entered a header name in cell B2.
Formula in cell B3:
 Double click on cell B3.
 Paste above array formula, shortcut CTRL + v.
 Press and hold CTRL and SHIFT keys simultaneously
 Press Enter once
The formula in the formula bar now looks like this: {=formula}
Don't enter these curly parentheses yourself, they appear automatically if you did the above steps correctly.
Explaining formula in cell B3
Step 1  Create an array
The OFFSET function allows you to identify filtered values if you combine it with the SUBTOTAL function.
OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))1, 0, 1)
becomes
OFFSET(Table2[First Name], {0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, 0, 1)
and returns
{"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}.
This step may seem weird when you can just use a cell reference to the values, however, that won't work. This step is necessary in order to force the SUBTOTAL function to evaluate each value in the array.
Step 2  Identify filtered values
The SUBTOTAL function returns 0 (zero) if value is not visible and 1 if the value is visible.
SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))1, 0, 1))
becomes
SUBTOTAL(3, {"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"})
and returns
{0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1}
Step 3  Return row numbers of filtered values
The IF function returns the corresponding row number of each filtered (visible) value and a blank for not visible values.
IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))1, 0, 1)), MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), "")
becomes
IF({0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1}, MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), "")
becomes
IF({0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}, "")
and returns
{""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; ""; ""; ""; 20}
Step 4  Extract the kth smallest row number
The SMALL function extracts the kth smallest number in a cell range or array.
SMALL(array, k)
SMALL(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))1, 0, 1)), MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), ""), ROWS($A$1:A1))
becomes
SMALL({""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; ""; ""; ""; 20}, ROWS($A$1:A1))
becomes
SMALL({""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; ""; ""; ""; 20}, 1)
and returns 10.
Step 5  Return value based on row number
The INDEX function returns a value based on a row number (and column number if needed).
INDEX(Table2[First Name], SMALL(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))1, 0, 1)), MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), ""), ROWS($A$1:A1)))
becomes
INDEX(Table2[First Name], 10)
becomes
INDEX({"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}, 10)
and returns "Fraser" in cell B4 which is the 10th value in the array.
The IFERROR function removes errors that will show when there are no more values to show.
Create named range
 Go to tab "Formulas" on the ribbon.
 Click "Name Manager" button.
 Click "New.." button to open the Name Manager dialog box.
 Name: Drop_down_list
 Refers to: =INDIRECT("Calculations!$B$3:$B$"&SUMPRODUCT((Calculations!$B:$B<>"")*1)+1)

Click OK!
 Click Close!
Explaining named range formula
The formula used in name range Drop_down_list is dynamic and changes based on the number of values in column B in worksheet "Calculations.
Step 1  Identify non empty cells in column B
The less and greater than signs combined checks if cells are empty or not.
Calculations!$B:$B<>""
becomes
{""; "Filtered Table values"; "Fraser"; "Jui"; "Kaya"; "" ... ""}<>""
and returns
{"FALSE";"TRUE";"TRUE";"TRUE"; "TRUE"; "FALSE"... ; "FALSE"}
It is not neccessary to check every cell in column B, if this calculation is slow for you then use a smaller cell reference, for example: Calculations!$B$1:$B$1000<>""
Step 2  Count non empty cells in column B
The SUMPRODUCT function returns a number representing the total number of non empty cells.
SUMPRODUCT((Calculations!$B:$B<>"")*1)
becomes
SUMPRODUCT({"FALSE";"TRUE";"TRUE";"TRUE"; "TRUE"; "FALSE"... ; "FALSE"}*1)
The SUMPRODUCT function can't sum boolean values (TRUE or FALSE) so we need to convert them to their numerical equivalents, this is done by multiplying with 1.
SUMPRODUCT({0; 1; 1; 1; 1; 0... ; 0})
and returns 4.
Step 3  Create cell reference
The ampersand allows you to combine text with a calculation.
"Calculations!$B$3:$B$"&SUMPRODUCT((Calculations!$B:$B<>"")*1)+1
becomes
"Calculations!$B$3:$B$"&4+1
becomes
"Calculations!$B$3:$B$"&5
and returns
"Calculations!$B$3:$B$5"
Step 4  Convert text to cell reference
The INDIRECT function lets you use the text as a cell reference.
INDIRECT("Calculations!$B$3:$B$"&SUMPRODUCT((Calculations!$B:$B<>"")*1)+1)
becomes
INDIRECT("Calculations!$B$3:$B$5")
and returns the values in cell range Calculations!$B$3:$B$5.
Create drop down list
 Select cell D26
 Go to tab "Data" on the riboon.
 Click "Data Validation" button.

Allow: List
 Source: =Drop_down_list
 Click Ok
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We [โฆ]
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a dropdown list with unique distinct alphabetically sorted values? Table of contents Sort values using [โฆ]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. [โฆ]
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to [โฆ]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection [โฆ]
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in [โฆ]
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves [โฆ]
Macro creates links to all sheets, tables, pivot tables and named ranges
This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, [โฆ]
7 Responses to โPopulate drop down list with filtered Excel Table valuesโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
It's a nice concept but, when you select Germany in you example, it fails. I think if filtered rows are in consecutive order, it works.
Fowmy,
You are right! It fails, my fault. It looks like it canยดt be done without using a helper column. I donยดt like "helper" columns. Data Validation lists canยดt handle non contiguous ranges.
I even tried creating a named range (and udf) returning a text string using a comma as a delimiter, but that failed too.
I am not sure what to do with this post.
Here is an example file with a "helper" column:
Addfilteredtablevaluestodropdownlist2.xlsm
The helper column is in sheet2.
I believe that you can get your idea to work if you create a custom function that returns a delimited string from the filtered cell values. Just be sure to credit me :)
David Hager,
I canยดt get it to work unless I use a named range. It seems the "drop down list" only accepts a cell range, a named range or a text string.
I used this function:
I can only get the custom function to work if I use it in a named range. But then the comma (text delimiter) wonยดt work. The drop down list shows all values in the same row.
What am I missing?
All credit goes to you :)
Id like to make one that is the opposite of what your posted,
ie. the the column gets filtered based on the value you select from a drop down, the drop down is populated with all the unique values of whats present in your table's column
Function VisibleValues(Rng As Range) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Rng
If Cell.EntireRow.Hidden = False Then
Result = Result & Cell.Value & Application.International(xlListSeparator)
End If
Next
VisibleValues = Left(Result, Len(Result)  2)
End Function
Rรผdiger,
Thank you for your contribution.
I had to remove only 1 character to make it work for my example: