## 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 drop-down list contains a named range that will fetch the extracted values from the new worksheet.

#### Calculation worksheet

- Press with right mouse button on on any worksheet tab at the very bottom of the Excel screen.

- Press with mouse on "Insert" to open a dialog box.

- Select "Worksheet" icon.
- Press with left mouse button on OK button.

To rename the worksheet simply press with right mouse button on on the worksheet tab and then press with left mouse button 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 press with left mouse button 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 k-th smallest row number**

The SMALL function extracts the k-th 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.
- Press with left mouse button on "Name Manager" button.
- Press with left mouse button on "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)
- Press with left mouse button on OK!

- Press with left mouse button on 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.
- Press with left mouse button on "Data Validation" button.
- Allow: List

- Source: =Drop_down_list
- Press with left mouse button on Ok

### Drop down lists category

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]

Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]

This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]

Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]

This article describes how to create a map in Excel, the map is an x y scatter chart with an […]

Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

### Table category

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]

The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]

In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]

This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]

This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]

Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]

The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]

This article explains how to calculate the largest and smallest number based on a condition which is if the number […]

### Named range category

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]

This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]

Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]

## Functions in this article

More than 1300 Excel formulas

## How to use Excel Tables

## Excel categories

### 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:

Add-filtered-table-values-to-drop-down-list2.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: