This isn’t a new issue, but it’s new to me. Charlie, a loyal reader, was trying to use INDIRECT with a dynamic range name and kept getting errors.
returns the #REF! error. It appears to be a limitation of INDIRECT (yes, another one).
One way to get around the problem is to just reproduce the dynamic range name formula in the cell.
That works, but now when you change List2, you have to remember to change this formula too, not to mention all the other formulas that use this.
Option #2 is a UDF.
‘It stands for Dynamic Indirect
Dim nName As Name
‘Make sure the name supplied exists
On Error Resume Next
Set nName = ActiveWorkbook.Names(sName)
Set nName = ActiveSheet.Names(sName)
On Error GoTo 0
‘Set the function to the range or return the name error
If Not nName Is Nothing Then
Set DINDIRECT = nName.RefersToRange
DINDIRECT = CVErr(xlErrName)
Gee, as simple as that function is you’d think Microsoft would have put in the program.
Does anyone have an option #3?