Referencing Dynamic Named Range in Excel Formula

Question

I have a table in Excel with column headings that correspond to part of a dynamic named range elsewhere in my workbook. For example, I have these column headings: "10", "20", etc., and these dynamic named ranges: "ExampleRange10", "ExampleRange2", etc. I'd like to enter a VLookup formula that references ExampleRange10 by concatenating the string "ExampleRange" and the column heading "10". This would allow me to simply extend the formula across all columns in the table, instead of manually typing "ExampleRange10", "ExampleRange20", etc. in each column's formula.

I'm aware of the INDIRECT function, and have used it successfully in the past with named ranges, but it doesn't seem to be working with the dynamic named range in this case. I'm guessing this is a nuance that has something to do with how dynamic named ranges are defined by Excel (they don't show up in the named range dropdown to the left of the formula bar, and they have some interesting properties in VBA, for example). Is there a way I can use the INDIRECT formula in conjunction with a dynamic named range, or is there another way that I can go about solving this problem?

Edit: Here are the exact formulas used.
This is the main formula: =VLOOKUP(B2,INDIRECT("ExampleRange"&C1),2,FALSE) where C1 contains "10" and the formula for my dynamic named range called "ExampleRange10" is: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$25),2). The main formula returns "#REF!", but it works correctly when I remove the dynamic named range formula and simply define "ExampleRange10" as a static range.

Foamy

Could you please provide EXACT formula which does not work? – Trichinopoly

You could maybe use some combination of offset() and column(), depending on your exact layout. – Segalman

@KyleWurtz =VLOOKUP(B5,INDIRECT("range"&C1),1,0) works fine for me in Excel 2007. Are you sure the rest of formula has no errors, e.g. 3rd parameter - column number in range to return? – Trichinopoly

@PeterL. Huh. And your "range10" is dynamic? I've double checked my formulas and ranges and I couldn't find any errors. When I go to Name Manager and click in the "Refers to" box, both the dynamic range and the static range highlight the same region, so I don't think the issue is with the OFFSET formula for the dynamic range...and as I mentioned before, the exact same VLOOKUP formula works when I substitute the beginning of the static range text for "ExampleRange", which is the beginning text for the dynamic range, so the VLOOKUP formula seems to be working too... – Foamy

@KyleWurtz you're right - double dynamics don't work indeed... perhaps intermediate cell where you obtain "ExampleRange"&C1 for further indirect is an option? – Trichinopoly

@PeterL. I tried that, but without luck. It looks like the underlying issue is that INDIRECT just doesn't work with dynamic named ranges... I'm currently looking into writing a user-defined function in VBA as a workaround. See link – Foamy

Answer 1

As best I could tell after doing further research, Excel's INDIRECT function simply doesn't work with dynamic ranges. There might be a clever way to get around using INDIRECT and sticking to the non-VBA Excel world, but I'm unaware of such a way. Instead, I ended up creating a user-defined function very similar to the one described here. I altered my main formula to read =VLOOKUP(B2,DINDIRECT("ExampleRange"&C1),2,FALSE), where DINDIRECT is the name of the VBA function I created.

The only downsides (which may or may not be downsides depending on how you look at it) to this alternative is that the workbook must be saved as a macro-enabled workbook and the use of a custom function isn't very self-documenting and requires a little explanation to other users. All things considered, though, this was an acceptable solution for me.

For the link-averse, here's the code:

Public Function DINDIRECT(sName As String) As Range
     Dim nName As Name

     On Error Resume Next
          Set nName = ActiveWorkbook.Names(sName)
          Set nName = ActiveSheet.Names(sName)
     On Error GoTo 0

     If Not nName Is Nothing Then
          Set DINDIRECT = nName.RefersToRange
     Else
          DINDIRECT = CVErr(xlErrName)
End Function

Note: Although this solution worked, I'm not going to accept my answer because I don't want to discourage others from posting better solutions. Also, I'm new to the site, so sorry if I'm breaking any etiquette codes by answering my own question...I just thought I'd share the exact solution that I used in case others find it useful.

Foamy

Answer 2

I hit this exact brick wall recently and the answer as you have already guessed is simply that you can't reference dynamic named ranges with INDIRECT.

You can however use the dynamic range formula itself as INDIRECT's argument, but this is no use for what you want to do. Somewhat of a PITA since it's the kind of functionality that would be very useful.

Overbalance

Thanks for confirming that INDIRECT doesn't work with dynamic named ranges. I wholeheartedly agree that this functionality would be useful, and it's especially frustrating given how simple the workaround code is (so it should be very easy for them to incorporate it) and how often the problem comes up (at least for me). – Foamy

Exactly. It's doubly frustrating when you have to come up with a solution that doesn't use UDFs (or VBA in any way) as I am often required to do. – Overbalance

Answer 3

If your data has headers like 10, 20 etc., then you don't need to use Indirect. Why not just use Index/Match to select the data you need?

Name your whole table ExampleRanges for example and use this formula:

Index(ExampleRanges, match(B2, index(ExampleRanges, , 1), 0), match(C1, index(ExampleRanges, 1,), 0))

Oyez

Worked! Using Index solved my problem with Indirect in named formula :) – Accepter

Answer 4

I know this is quite old, but I only just came across this and thought I'd add a solution that avoids any VBA coding in case it helps anyone else who stumbles across this:

=VLOOKUP(B2,CHOOSE(C1/10,example10,example20,example30,example40),2,0)

This is assuming the naming convention being 10,20,30,etc and will not be ideal for hundreds of ranges.

Woodenware

Answer 5

Untested, but I think this would work:

user defined function to return the address of your dynamically named range:

Function Named_Range_Address(Range_Name As Range, _ 
    Optional SheetName As Boolean) As String 

    Dim strName As String 
    Application.Volatile 

    If SheetName = True Then 
        strName = "'" & Range_Name.Parent.Name & "'!" & Range_Name.Address 
    Else 
        strName = Range_Name.Address 
    End If 

    Named_Range_Address = strName 
End Function 

then you should be able to use your vlookup formula:

=VLOOKUP(B2,INDIRECT(named_range_address("ExampleRange"&C1,TRUE)),2,FALSE)

Flossieflossy

Thanks for the suggestion. I saw a function like this somewhere when I was googling, but this particular function requires the first argument to be of type Range, but since I'm concatenating the strings "ExampleRange" and the text in C1, I get an argument of type String. However, your suggestion to use a user-defined function is right on. I did find another function that takes the RangeName As String here, and that worked fine for me. – Foamy

Answer 6

Today I was tinkering with Excel named ranges, and I discovered that, while it is true that you cannot compute the name of the range in the INDIRECT() call itself, you can still get it in a pure "Excel-way" by adding an intermediate step: just create some hidden cell in which you compute the named range.

For example, say that A1 contains the "dynamic part" of the range name, then in A2 use the formula = "ExampleRange" & A1, and now you have the full range name, which you can use as = INDIRECT(A2).

Monition

It's not so much the name itself being dynamic that's the problem, it's when the range to which it refers is dynamic. – Overbalance

Answer 7

Adding a new twist, it is possible to use a named range with the Address and Indirect functions. I have a case where I am setting named ranges for a series of tables and am using the following:

Named Range: WWDH-FF-PI which points to Linear!$A$19 (first cell in table)

to get the address: $T$56: =ADDRESS(MATCH(S56,Linear!A:A,0),1,1,1,"Linear")

Then using the offset function copied multiple times to create a pivot table:

=OFFSET(INDIRECT($T$56),C5,$T$57-1)

So, the Address function can be embedded (or wrapped) into the Indirect function to create a dynamic cell address.

Science

That works, but unfortunately the problem occurs with dynamic named ranges, i.e. a named range which refers to a variable number of cells, usually depending on their contents. – Overbalance

Answer 8

I know this is a really old thread, but I had the same issue, so perhaps my solution can help people in the future.

Basically, I created a Macro that would delete and re-define the range upon save, and give it a name. Therefore, the INDIRECT function would work as the range was not dynamic. All you need to do is save the workbook after adding any values to the named ranges

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim a, b, c, d, e, f As Integer
Dim data As Worksheet

Set data = ThisWorkbook.Worksheets("Data")

a = data.Range("A" & Rows.count).End(xlUp).row
b = data.Range("B" & Rows.count).End(xlUp).row
c = data.Range("C" & Rows.count).End(xlUp).row
d = data.Range("D" & Rows.count).End(xlUp).row
e = data.Range("E" & Rows.count).End(xlUp).row
f = data.Range("F" & Rows.count).End(xlUp).row



ActiveWorkbook.Names("KP").Delete
ActiveWorkbook.Names("KPT").Delete
ActiveWorkbook.Names("AP").Delete
ActiveWorkbook.Names("APT").Delete
ActiveWorkbook.Names("DISC").Delete
ActiveWorkbook.Names("SEATS").Delete

ActiveWorkbook.Names.Add Name:="KP", RefersTo:="=Data!$A$2:$A$" & a
ActiveWorkbook.Names.Add Name:="KPT", RefersTo:="=Data!$B$2:$B$" & b
ActiveWorkbook.Names.Add Name:="AP", RefersTo:="=Data!$C$2:$C$" & c
ActiveWorkbook.Names.Add Name:="APT", RefersTo:="=Data!$D$2:$D$" & d
ActiveWorkbook.Names.Add Name:="DISC", RefersTo:="=Data!$E$2:$E$" & e
ActiveWorkbook.Names.Add Name:="SEATS", RefersTo:="=Data!$F$2:$F$" & f

End Sub

Countercurrent