Concatenating a Dynamic Range Name

G

gamestas

New Member
Joined
Apr 24, 2003
Messages
4
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 :cry:
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
I've never been able to do it. I got around it in some sheets that I use all the time by re-setting the ranges via VBA on refresh of my data.

Perhaps someone here has worked out how to do it via a UDF or some such, but for mine, no, I don't think it can be done, unfortunately.
 
Upvote 0
Something in the result of the various cells you are looking up (or the MATCH) is causing the problem. A simple test of using MATCH() that references a cell that, in turn, contains a named range works just fine.
Code: 
=MATCH(1,INDIRECT(D3),FALSE)
For the above test, I named two ranges in a worksheet as col1 and col2 respectively. I then entered col1 in cell D3 and used the above formula. Changing the contents of D3 from col1 to col2 looks up the value in the other range.
 
Upvote 0
Hi gamestas:

Welcome to the Board!

I do not have an answer for you -- but I wanted to share my own difficulty with some formulations using the INDIRECT function.

I have often encountered difficulty with formulas using the INDIRECT function -- my INDIRECT formulation would be working say fine with a cell reference for a certain parameter, but on my trying to generalize that particular parameter with a range name (or another INDIRECT formulation), the formula would conk out. I personally don't have an explanation for myself, but I surmise, that it is for the increased level of complexity that gets introduced. I myself am curious in finding the specific cause for such behaviour.
 
Upvote 0
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....:)
 
Upvote 0
tusharm said:
Something in the result of the various cells you are looking up (or the MATCH) is causing the problem. A simple test of using MATCH() that references a cell that, in turn, contains a named range works just fine.
Code: 
=MATCH(1,INDIRECT(D3),FALSE)
For the above test, I named two ranges in a worksheet as col1 and col2 respectively. I then entered col1 in cell D3 and used the above formula. Changing the contents of D3 from col1 to col2 looks up the value in the other range.
Click to expand...

Thanks everyone for the replies. Tushar, I've tried what you've suggested by creating the following formula:
=CONCATENATE("'",LoanTerm," Mon Pricing'!","Lookup_",LoanTerm,"_60Day")
in a cell I've named FormRef. In and of itself, it seems to build the string I need just fine. I've then updated my original formula as follows:
=MATCH(ConvCost,INDIRECT(FormRef),1)
But to no avail. I'm still getting the #REF error.

Am I misinterpreting your suggestion? Does anyone have other ideas?

Thanks again,
Greg
 
Upvote 0
When you go to the Insert | Name dialog, and you select one of your dynamic ranges, and then click on the "Refers To" box, do you see the 'marching ants' ? if not, then the problem is in the definition of the range, if yes, make sure that it is using the expected range.
 
Upvote 0
Juan Pablo González said:
When you go to the Insert | Name dialog, and you select one of your dynamic ranges, and then click on the "Refers To" box, do you see the 'marching ants' ? if not, then the problem is in the definition of the range, if yes, make sure that it is using the expected range.
Click to expand...

Thanks for the suggestion. I tried the Insert | Name check that you described, but I see the "marching ants", so I wouldn't think the range definition is the problem. Also, I've tried recreating the formula using the formula for the dynamic range itself (rather than its name):

=MATCH(ConvCost*100,INDIRECT(CONCATENATE("'",LoanTerm," Mon Pricing'!","Start_",LoanTerm,"_60Day",":OFFSET('",LoanTerm," Mon Pricing'!","Start_",LoanTerm,"_60Day","COUNTA('",LoanTerm," Mon Pricing'!","Start_",LoanTerm,"_60Day",":",COLUMN(INDIRECT(CONCATENATE("Start_",LoanTerm,"_60Day"))),")-1,0)")),1)
where CONCATENATE("Start_",LoanTerm,"_60Day") represents the anchor cell for the dynamic range. No luck here either.

Greg
 
Upvote 0
Mr Walker said:
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....:)
Click to expand...

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.
 
Upvote 0
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
Click to expand...

But INDEX(A:A,1) should return a valid refernce to A1...
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Combining Filter function with Indirect/Concatenate to look up from another worksheet
Replies
3
Views
137
Fluff
Fluff
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
138
Nick Loughton
N
K
  • Question
SUMIFS and dynamic named ranges
Replies
3
Views
178
awoohaw
awoohaw
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
266
ashleighbrown
A
Keebler
  • Question
Find lastest date in range
Replies
5
Views
74
Keebler
Keebler

Forum statistics

Threads
1,214,656
Messages
6,120,762
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top