INDIRECT dynamic named range in Excel 2007

[Andy Nguyen]

If I remember correctly, referring to dynamic named range has always been a problem in Excel. I'm curious to know if it has been addressed in Excel 2007. If not, has anyone found a way to work around this problem?
Included is a small example sheet in Excel 2007 format. You can convert it to 2003 format.

Tools: Excel 2007
Problem: Excel not recognizes dynamic named range that I created using OFFSET.

Steps: I created a named range called CumRisk_5Y_OFFSET with this formula
=OFFSET(Sheet1!$D$16,0,0,1,13)

then in the sheet, I refer to it by this formula
=OFFSET(INDIRECT("CumRisk_"&TermX&"_OFFSET"),0,0,1,1) where TermX = 5Y which failed.

 

[Adam]

This seems to be a known bug in Excel, and I'm not terribly shocked that they haven't fixed it in 2007. Sorry to say, I've got no simple solution for you. In this particular case, I don't really see the need for using dynamic ranges in the first place. Is this just a test case you sent because of some other, more complex, spreadsheet with a similar issue? Why use dynamic ranges?

 

[Andy Nguyen]

Adam,
Yes, this example is just a test case. Almost all our data is dynamic, hence the need for dynamic range. For example, in the formula OFFSET(Sheet1!$D$16,0,0,1,13) except for the first parameter, everything else needs to be dynamic.

I found a workaround with a function that will return the address of my dynamic range so I can just INDIRECT with that address.

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[/high]So now I can use it as
=OFFSET(INDIRECT(Named_Range_Address(CumRisk_5Y_OFFSET)),0,0,1,1)

Now, comes this easy question, how can I replace 5Y in the CumRisk_5Y_OFFSET name by TermX without making it a string.
The function Named_Range_Address only takes range, not string so I can't append the TermX using &

 

[Andy Nguyen]

I'm answering my own queries here.
To avoid the problem of using range name, the solution is to string as name and then use VBA function REPLACE to dynamically change it.

Public Const gstr_TRV_TERMx_NR As String = "TermX"
Public Const gstr_TRV_TERMy_NR As String = "TermY"
Function Named_Range_Address(Range_Name As String, _
SheetName As String) As String
 
Dim strName As String
Dim ws As Excel.Worksheet
 
Application.Volatile
 
Set ws = ThisWorkbook.Worksheets(SheetName)
 
strName = Range_Name
 
strName = Replace(Range_Name, "termx", ws.Names(gstr_TRV_TERMx_NR).RefersToRange.Value)
strName = Replace(strName, "termy", ws.Names(gstr_TRV_TERMx_NR).RefersToRange.Value)
 
Named_Range_Address = ws.Names(strName).RefersToRange.Address
 
End Function

I then call it this way
=OFFSET(INDIRECT(Named_Range_Address("CumRisk_termx_OFFSET","Sheet1")),0,0,1,1)

 

[Adam]

Another option is to simply rewrite the INDIRECT function. Quick and dirty gets you...

Function BINDIRECT(strRange as String) As Range
 
Set BINDIRECT = Range(strRange)
 
End Function

=OFFSET(BINDIRECT("CumRisk_"&TermX&"_OFFSET"),0,0,1,1) should work just fine.

 

[Andy Nguyen]

The best solution is often the simplest one. Thanks, Adam.