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
Set Result = Application.Union(Result, Cell)
Set VisibleValues = Result