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

  1. Go to tab "Formulas"
  2. Click "Name Manager" button
  3. Click "New.." button
  4. Name: Test
  5. Refers to: =VisibleValues(Table2[First Name])
  6. Click OK!
  7. Click Close!

Create drop down list

  1. Select cell C25
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Allow: List
  5. Source: =test
  6. Click Ok

Download excel *.xlsm file

Add filtered table values to drop down list.xlsm