Named range error

[Guest]

I am naming a range based on the output of two ADDRESS() formulas, using the
format =INDIRECT(A1):INDIRECT(A2), where A1 and A2 are outputs of my
ADDRESS() formulas. This seems to work, except that when I try to call the
named range for a formula, it crashes excel after the first or second try.
For example, if I type in SUM(range_name) it crashes about 8 times out of 10;
in the 2 instances it doesn't crash, it crashes when I enter
COUNT(range_name) in another cell. What gives?

 

[Guest]

I think you're asking for the technical reason this fails? I don't have the
definitive answer. I suspect that it is the use of INDIRECT twice in
defining the 'refers to' property of the named range. But I haven't been
able to find a reference to a rule that says you can't do it - there's very
little written about defining named ranges using anything other than a
regular range reference of some type without anything added like INDIRECT().

I can tell you that I tried it and it failed immediately for me when I tried
a SUM() on a named range defined like that. Of course, using
=SUM(INDIRECT(A1):INDIRECT(A2)) worked just fine.

 

[Guest]

I've worked around the problem by using just one INDIRECT() formula in my
range definition, referring to a cell where I've concatenated ADDRESS(1) &
":" & ADDRESS(2); this seems to work.

 

[Guest]

A+ for ingenuity.

I apologize for not offering that as a solution - I thought you were just
looking for the "why" of it not working the way you had it set up initially.