INDIRECT & Dynamic Ranges - what am I missing?

The function =ROWS(TestRange1) evaluates successfully to 20;while the function =ROWS(INDIRECT("TestRange"&"1")) coughs up a #REF!.

Click to expand...

All I can suggest is that Indirect() being a volotile function, doesn't like being nested. Here is a simpler breakdown of what you have done (I think) without the confusion of the Named Range.

Notice that the formula in A8 operates as expected, while A10 does not. I think difference is related to the structure of the Dependency Table. In one case, you could categorize the relationship as Cascaded, while the other is Nested. I think in your case, you are creating a Nested relationship, which can be problematic with Nested functions which are also Volatile (as Indirect is).

I think the most reasonable advice I can give you is related the patient who says "Doctor, it hurts when I do THIS."

Don' do it.

That being said, what are you trying to accomplish... perhaps we can come up with an alternative...

INDIRECT in:

=ROWS(INDIRECT("TestRange"&"1"))

expects a string that can be resolved as a reference.

While TestRange1, which is INDIRECT(Sheet1!$A$1), resolves to an array object.

And yet, this works:

Which also gives lie to my initial evaluation... I think I am even more confused now.

INDIRECT in:

=ROWS(INDIRECT("TestRange"&"1"))

expects a string that can be resolved as a reference.

While TestRange1, which is INDIRECT(Sheet1!$A$1), resolves to an array object.
~Aladin

Click to expand...

Aladin,

Can you elaborate? (Or link to where you have already elaborated if you can?) To what "array" object is this resolving to? I have bumped my head on this kind of thing myself and I can never quite suss out why it fails.

edit: Hiya, Paul. Trust you've been well.

INDIRECT in:

=ROWS(INDIRECT("TestRange"&"1"))

expects a string that can be resolved as a reference.

While TestRange1, which is INDIRECT(Sheet1!$A$1), resolves to an array object.
~Aladin

Click to expand...

Aladin,

Can you elaborate? (Or link to where you have already elaborated if you can?) To what "array" object is this resolving to? I have bumped my head on this kind of thing myself and I can never quite suss out why it fails.

edit: Hiya, Paul. Trust you've been well.

Click to expand...

Let A2:A5 on Sheet1 house:

2
3
2
5

Let A1 on Sheet1 house:

Sheet1!$A$2:$A$5

and define TestRange1 by means of:

[1]

=INDIRECT(Sheet1!$A$1)

F9 applied on [1] yields an array object, that is:

={2;3;2;5}

whose items are not strings, each of which would evaluate just to itself: 2 ==> 2, etc.

[2]

=INDIRECT(TestRange1)

==> #REF!,

because TestTange1 evaluates to ={2;3;2;5}, whose items INDIRECT cannot resolve to a refernce.

[3]

=TestRange1

which is the same thing as [1] and will evaluate to: ={2;3;2;5}.

[4a]

=INDIRECT("TestRange1")

or

[4b]

=INDIRECT("TestRange"&1)

would both evaluate to #REF! for TestRange1 presumably ==> {2;3;2;5}, whose items are unresolvable.

Hi tdhood:

Unless I am missimng something here, for me on EXCEL2000 on Windows XP, both of the following worked ...

=ROWS(TestRange1)

as well as

=ROWS(INDIRECT("TestRange"&"1"))

In the following illustration I have used the above two and several other combinations ...

Unless I am missimng something here, for me on EXCEL2000 on Windows XP, both of the following worked ...

=ROWS(TestRange1)

as well as

=ROWS(INDIRECT("TestRange"&"1"))

In the following illustration I have used the above two and several other combinations ...

Click to expand...

Yogi -

In your example, are you explicity defining "TestRange1" in the "Define Name" box? If so, then yes; it will behave properly. My difficulty comes when I try to dynamically define "TestRange1" in the "Define Name" box, using another INDIRECT statement.

Try re-working your example using =INDIRECT(Sheet4!$A$1) to define "TestRange1" & see if any of your formulas blow up.

tdh

Aladin,

As always, much obliged for the lesson.¹ This just got added to my list of favorites to use for future reference.<hr />¹(I've lost count of how many times I've said this.)

....
Yogi -

In your example, are you explicity defining "TestRange1" in the "Define Name" box? If so, then yes; it will behave properly. My difficulty comes when I try to dynamically define "TestRange1" in the "Define Name" box, using another INDIRECT statement.

Try re-working your example using =INDIRECT(Sheet4!$A$1) to define "TestRange1" & see if any of your formulas blow up.

tdh

Click to expand...

Thanks for the clarification tdhood. Let us have another look ...