Use filtered table values in a drop down list (vba)
I read a very interesting blog post about Using Custom Functions in Dynamic Ranges
Gabhan Berry creates a user defined function and uses it in a named range. That is something I have never seen before and it inspired me to write this blog post.
The animated gif below demonstrates how values (First Name) in a drop down list changes depending on how the table is filtered (Country).
User defined function
Function VisibleValues(Rng As Range) As Range
Dim Cell As Range
Dim Result As Range
For Each Cell In Rng
If Cell.EntireRow.Hidden = False Then
If Result Is Nothing Then
Set Result = Cell
Else
Set Result = Application.Union(Result, Cell)
End If
End If
Next
Set VisibleValues = Result
End Function
Create named range
- Go to tab "Formulas"
- Click "Name Manager" button
- Click "New.." button
- Name: Test
- Refers to: =VisibleValues(Table2[First Name])
- Click OK!
- Click Close!
Create drop down list
- Select cell C25
- Go to tab "Data"
- Click "Data Validation" button
- Allow: List
- Source: =test
- Click Ok
Download excel *.xlsm file
Add filtered table values to drop down list.xlsm
Related posts:
Use a drop down list to extract and concatenate unique distinct values
Count unique distinct values in a filtered table
Extract unique distinct values from a filtered table (udf and array formula)
Change pivot table data source using a drop down list
Excel charts: Use dynamic ranges to add new values to both chart and drop down list
Recommended categories
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:
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 & ", " End If Next VisibleValues = Left(Result, Len(Result) - 2) End FunctionI 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