Link to dynamic named range in Excel formula

Question

I have a table in Excel with column headers that correspond to part of a dynamic named range elsewhere in my book. For example, I have these column headings: "10", "20", etc., And also these dynamic named ranges: "ExampleRange10", "ExampleRange2", etc. I would like to introduce a VLookup formula that refers to ExampleRange10 by concatenating the string “ExampleRange” and the column heading “10”. This would allow me to simply expand the formula across all columns in the table, instead of manually entering "ExampleRange10", "ExampleRange20", etc. In each formula column.

I know the INDIRECT function and have used it successfully in the past with named ranges, but in this case it doesn't seem to work with a dynamic named range. I assume this is a nuance that has something to do with how dynamic named ranges are defined by Excel (they don't appear in the drop-down list of named ranges to the left of the formula bar, and they have some interesting properties in VBA, for example). Is there a way to use the INDIRECT formula in conjunction with a dynamic named range, or is there another way to solve this problem?

Change Specific formulas are given here.
This is the basic formula: =VLOOKUP(B2,INDIRECT("ExampleRange"&C1),2,FALSE) where C1 contains "10", and the formula for my dynamic named range called "ExampleRange10": =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 formula of the named range and simply define “ExampleRange10” as a static range.

Answer 1

As far as I could tell after further research, the Excel INDIRECT function just doesn't work with dynamic ranges. There may be a smart way to get INDIRECT using INDIRECT and stick to the world of non-VBA Excel, but I don't know this way. Instead, I created a custom function very similar to the one described here . I changed my basic 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 reduced depending on how you look at it), is that the workbook should be saved as a macro-enabled workbook, and the use of a custom function is not very documentation and needs a little explanation to other users. However, all this was considered an acceptable solution for me.

If there is no link here is 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. Despite the fact that this solution worked, I will not accept my answer because I do not want to prevent others from posting the best solutions. Also, I'm new to the site, so sorry if I break any etiquette codes by answering my own question ... I just thought I'd share the exact solution I used if others found it useful.

Answer 2

I recently hit this exact brick wall, and the answer, you guessed it, just means that you cannot reference dynamic name ranges with INDIRECT.

However, you can use the dynamic range formula as an INDIRECT argument, but this is useless for what you want to do. To some extent PITA, as it was a feature that would be very useful.

Answer 3

If your data has headers like 10, 20, etc., you do not need to use indirect ones. Why not just use Index / Match to select the data you need?

Name the entire table ExampleRanges, for example, and use this formula:

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

Answer 4

Unconfirmed, but I think this will work:

a custom function returns 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 can use the vlookup formula:

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

Answer 5

I know this is pretty old, but I just came across this and thought that I would add a solution that would avoid VBA coding if it helps someone else who came across this:

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

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

Answer 6

Today I went over Excel ranges with names, and I found that while it’s true that you cannot calculate the range name in the INDIRECT() call INDIRECT() , you can still get it in a pure “Excel path” by adding an intermediate step: simply create a hidden cell in which you are calculating the named range.

For example, let's 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) .

Answer 7

By adding a new twist, you can use a named range with address and indirect functions. I have a case where I set named ranges for a series of tables and I use 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 several times to create a pivot table:

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

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

Answer 8

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

Basically, I created a macro that deletes and redefines the range when saved and gives it a name. Therefore, the INDIRECT function will work because the range is not dynamic. All you have to do is save the book 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