INDIRECT and Dynamic Ranges

[polarisking]

Does Excel have issues using INDIRECT with a named range defined by a dynamic formula?

Example:

A1 = Chandoo (and named ChandooNR)
CI = ChandooNR

=INDIRECT(ChandooNR) returns the value Chandoo. It's what we'd expect.

But, when I create a dynamic named range called ChandooDNR defined by =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) and D1 = ChandooDNR

=INDIRECT(D1) returns #REF.

I'm convinced the formulas are correct, so I suspect this has something to do with Excel's inability to resolve nested volatile relationships.

Any thoughts?

 

[Peter Bartholomew]

I think it will even fail on a static range
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,2)

Something that does work is
= INDIRECT(ChandooDNR)
where ChandooDNR refers to
="Sheet1!$A$1:$A" & COUNTA(Sheet1!$A:$A)

I suspect it is more a case that INDIRECT is looking for a text string and not a reference.

 

[Debaser]

INDIRECT will not work with a name that is anything other than a simple static range. You can use the old XLM function EVALUATE but you have to use it a defined name and you'll have to save the workbook in a macro-enabled format, so Peter's method generally simpler.