Concatenating a Dynamic Range Name

[gamestas]

I'm currently using the following formula:

=MATCH(ConvCost,INDIRECT(CONCATENATE("'",LoanTerm," Mon Pricing'!$e$5:$e$30")))

as part of a larger formula to do a lookup on one of a group of other worksheets (where LoanTerm is a value from a list that dictates which sheet the data will be pulled from). The formula works fine as is. However, when I tried replacing "$e$5:$e$30" with the name of dynamic range (from a group of similarly named dynamic ranges), I get a #REF error. The name of the dynamic range to be used is being built by concatenation based on the value of LoanTerm:

=MATCH(ConvCost,INDIRECT(CONCATENATE("'",LoanTerm," Mon Pricing'!Lookup_",LoanTerm,"_60Day")))

What am I missing here? Is it not possible to dynamically generate the name of a dynamic range for use in INDIRECT?

Using XL97 without VBA.
TIA,
Greg

 

[Aladin Akyurek]

I see what you mean Aladin, but I think this is a strange behavior of the INDIRECT function.

I don't think it can handle functions that actually return a reference, because this

=INDIRECT("INDEX(A:A, 1)")

also returns #REF!

The help file says:

If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value

But INDEX(A:A,1) should return a valid refernce to A1...

=INDEX(A:A,1)

and

=INDIRECT("A1")

returns exactly the same object -- a value. Let 2 be that value.

=INDIRECT("INDEX(A:A,1)")

would be equivalent to:

=INDIRECT(2)

The latter fits the quote from the Help file, which does not always make a distinction between a reference and the value(s) a reference houses.

=INDEX(INDIRECT("A1"),0,1)

is a valid formula

because INDEX also accepts numbers as reference...

=INDEX(2,0,1)

 

[gamestas]

Yeah, but are your ranges dynamic Tushar?

Indirect works great if it's a static range, like $A$1:$A$50, but when you make the range dynamic, all of a sudden it doesn't like it....

Let A1:A4 in Sheet1 house the following...

{2;1;3;4}

1] Define DynRange (via Insert|Name|Define) as referring to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

2] Define also FiniteRange (e.g., via the Name Box) as referring to:

=Sheet1!$A$1:$A$4


In C1 enter: FiniteRange

In D1 enter:

=SUM(INDIRECT(C1))

which should result in 10.

In C2 enter: DynRange

In D2 enter:

=SUM(INDIRECT(C2))

which should result in #REF!.

I suppose that the reason for the difference in behavior lies in the fact DynRange consists of an already deferenced array of values while FiniteRange not.

I'm not sure this is the issue the OP is struggling with.

Aladin, this sounds very much like what I'm struggling with. I agree with your observations, and it sounds very much like INDIRECT may not be the proper tool for what I'm trying to accomplish. Unfortunately, I'm at a bit of a loss for how to accomplish what I'm trying without it. I will experiment with other lookup and reference functions to see what I can come up with and will update the post with my findings...

Thanks for your help

 

[Mr Walker]

I have struggled with this on occasion, and have never found a solution.

The simple answer is that you cannnot use the indirect function to refer to a dynamic range.

I consider it to be a bug myself.

I know, the shock of it all, a Microsoft product having a bug!

 

[Aladin Akyurek]

I have struggled with this on occasion, and have never found a solution.

The simple answer is that you cannnot use the indirect function to refer to a dynamic range.

I consider it to be a bug myself.

I know, the shock of it all, a Microsoft product having a bug!

I don't think it's a bug. I tried above to construct the reason. A dynamic named range is built with either OFFSET or INDEX (as I recently started to do). Both OFFSET and INDEX alredy delivers a deferenced reference. So...

=INDIRECT({....})

runs into #REF! because {...} is the wanted object.

 

[Juan Pablo González]

Aladin, but shouldn't INDIRECT "behave" in a similar fashion than F5 (Go to) ?

You can type in the GoTo box 'DynRange' and Excel will take you there... but the difference is that INDIRECT returns a reference to that object ?

 

[Aladin Akyurek]

Aladin, but shouldn't INDIRECT "behave" in a similar fashion than F5 (Go to) ?

You can type in the GoTo box 'DynRange' and Excel will take you there... but the difference is that INDIRECT returns a reference to that object ?

Returning to my example...

=INDIRECT("FiniteRange")

works, I think, like this...

"FiniteRange"

---> Sheet1!$A$1:$A$4

--(deferencing step)-->{2;1;3;4}


=INDIRECT("DynRange")

"DynRange"

---> {2;1;3;4} which is handed over by INDEX/OFFSET

--(deferencing step [that is, INDIRECT({2;1;3;4})]) --> #REF!

So, INDEX/OFFSET does not hand over Sheet1!$A$1:$A$4, rather {2;1;3;4}, which INDIRECT in deferencing step cannot deference, hence #REF!.