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!