Dependent drop down list using dynamic named ranges

Hi there,


I am trying to create a dependent drop down list using dynamic named ranges.


I found a thread which explains that the INDIRECT function does not work well with dynamic named ranges, and suggests using the below VBA as a workaround.


Option Explicit
Function RetrieveRangeForName(psRange As String) As String
RetrieveRangeForName = Range(psRange).Address
End Function


This seems very close to solving the issue. Using the VBA, I can get the dynamic named range to populate in the Excel sheet itself using the below:

=INDIRECT(RetrieveRangeForName(A2))

-- Cell A2 contains the dropdown list of dynamic named ranges --


But when I copy the above formula into the Data Validation source box, it comes up with a warning saying "A named range you specified cannot be found".


Any ideas what's going wrong?


Cheers
Tom