Issue using Dynamic Named Ranges for Cascading Data Validation

2
votes

I'm using two cells with Data Validation - the first cell (E9) simply creates a drop down menu based on the range A2:A6, and the second cell (E10) validation uses the source INDIRECT(E9), which will always refer to one of five different named ranges.

When I have the named ranges fixed, (i.e A2:A250), the second drop down works, but I really need the ranges to be dynamic, so far I've been creating named ranges with the following "source" formula:

=OFFSET(LookupLists!$B$2,0,0,COUNTA(LookupLists!$B:$B),1)

With the other ranges being the exact same only in columns C-F.

When I write out this formula it highlights the correct area on the screen, but the drop down button in cell E10 is completely unresponsive, when the drop down list should show the exact area that's being highlighted.

As a note, the lists themselves are created using an array formula and some VBA code to create a sorted unique list based on another part of the spreadsheet, so I've been unable to use tables to create the ranges as some other websites have suggested.

2
excelexcel-formula

2 Answers

2
votes

INDIRECT doesn't work with dynamic ranges. Credit to these guys for the solution:

http://chandoo.org/forum/threads/passing-a-named-range-to-a-formula-with-indirect.5854/#post-32423

First, insert a module into you sheet and paste in the UDF:

Option Explicit

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

Then you will need a helper cell, since I don't think UDFs work in the Data Validation dialog. In E11, enter =RetrieveRangeForName(E9).

Then in the Data Validation, set to List, you can enter: =INDIRECT(E11)

1
votes

The reason it doesn't work as discussed here is that INDIRECT expects a string that it can evaluate to give a reference. However your named range is already a reference. Logically, the only way to use it in INDIRECT is to convert it into a string first which you can do with a UDF:-

Function GetAddress(Name As String) As String
Dim rng As Range, addr As String
Set rng = Worksheets("Sheet1").Range(Name)
addr = rng.Address
GetAddress = addr
End Function

Then use this to define a range called NewRange:-

=INDIRECT(GetAddress(Sheet1!$E$9))

Finally this can be used in the validation for E10 (Named Range ListB is defined as in the question, ListA etc. correspondingly for columns A to E).

enter image description here