Excel 2007 Problem with INDIRECT to Named Array

[gtmiller]

[FONT=&quot]I have 20 worksheets that each have a 4-week training block for a University Athletic Program. Each worksheet has 5 named ranges for days of the week. The sheet for Block 1 has the named ranges: B1M, B1T, B1W, B1TH and B1F. From a Summary sheet, I have VLOOKUP formulas that each look in 1 specific named range. I have to insert the Name in thousands of formulas so I have listed the names in a column in the Summary sheet and then referenced the cell with Indirect from in the formula. Example B1M in cell C3 would have Indirect(C3) in the formula, but this causes the formula to become volatile and the workbook calculates very slowly. Is there a way to format the name in C3 or reference it in the formula so that it is not volatile?<o></o>[/FONT]

 

[T. Valko]

Depending on how you're using these named ranges in formulas you might be able to do something like this:

C3 = B1M

CHOOSE(MATCH(C3,{"B1M","B1T","B1W","B1TH","B1F"},0),B1M,B1T,B1W,B1TH,B1F)

You can create a name:

Name: Ranges
Refers to: ={"B1M","B1T","B1W","B1TH","B1F"}

Then use something like this:

CHOOSE(MATCH(C3,Ranges,0),B1M,B1T,B1W,B1TH,B1F)

 

[jim may]

You're gonna need " "'s around each String option

=CHOOSE(MATCH(C3,MyRange,0),"B1M","B1T","B1W","B1TH","B1F")

 

[Aladin Akyurek]

You're gonna need " "'s around each String option

=CHOOSE(MATCH(C3,MyRange,0),"B1M","B1T","B1W","B1TH","B1F")

Jim,

Since B1M, B1T, etc. are names in Excel's name space, CHOOSE is capable to pick up the range they stand for. Double quoting is required if they are fed to INDIRECT, but not here.

BTW, the OP seems to have many such names, so a single invocation of CHOOSE will not suffice. Some grouping might be in order.

 

[T. Valko]

You're gonna need " "'s around each String option

=CHOOSE(MATCH(C3,MyRange,0),"B1M","B1T","B1W","B1TH","B1F")

Those aren't string options. They're named ranges. The string options are defined in MyRange.

Let's assume C3 = B1TH

B1TH is a named range that refers to X1:X10.

=SUM(CHOOSE(MATCH(C3,MyRange,0),B1M,B1T,B1W,B1TH,B1F))

Will return the sum of X1:X10.

 

[jim may]

Sorry Guys, I jumped the Gun.
All is well..

 

[gtmiller]

I think it would help me if I posed this as a much simpler scenario. If I start with 2 columns on Sheet 1 of a Workbook that have Jan, Feb, March in column A and 1,2,3 in Column B and name the array Months. Then I go to sheet 2 and in cell A1 put =VLOOKUP("March",Months,2) I get the result 3.

But, I want to put the name of the array "Months" in cell B1 and refer to it from the VLOOKUP. If I put =VLOOKUP("March",INDIRECT(B1),2) it works but is Volatile. I need to know what to replace INDIRECT(B1) with to have it point to B2 and what to put in B2 so that VLOOKUP refers to the "Months" array.

Thanks for your help.

 

[T. Valko]

Try this:

=VLOOKUP(A1,CHOOSE(MATCH(B1,{"Months"}),Months),2,0)

 

[gtmiller]

Thanks, the following formula that you recommended works:
=VLOOKUP(A1,CHOOSE(MATCH(B1,{"Months"}),Months),2,0)

I am still trying to have the Array Name: Months be drawn into the formula by cell reference to B2 (Month). It works if I replace the "Months" with "B2" as in the following formula:
=VLOOKUP(A1,CHOOSE(MATCH(B1,{"B2"}),Months),2,0)

But, I still have another reference to the Months array in the formula that I need to reference back to the cell B2 (Months) without using INDIRECT and making the formula Volatile.

 

[T. Valko]

=VLOOKUP(A1,CHOOSE(MATCH(B1,{"B2"}),Months),2,0)

That may be working but it's just dumb luck. You're not referencing cell B2, you're finding the closest match that is less than or equal to the TEXT string entered in B1.

If the entry in B1 is greater than or equal to the TEXT string "B2" then the MATCH function finds a match. For example, if cell B1 contained the text entry "Months", "Months" is greater than the TEXT string "B2" so MATCH finds the the closest entry that is less than or equal to the text string "Months" and that closest entry is the text string "B2".

If B1 contained the text string "Assets" then the formula would return a #N/A error. The text string "Assets" is less than the text string "B2".

I originally wrote the formula in that fashion because I thought you had several possible named ranges to reference.

There is no way to directly use a cell reference that holds a defined name and refer to that range without using the INDIRECT function. The closest thing you can get is using some form of CHOOSE and as you can see it's not really the same thing but you do resolve the issue with volatile functions always recalculating.

Sometimes it's better to just bite the bullet and hardcode the reference in the formula.