Changing named range reference depending on a cell's content

[cparaske]

Where to start?!

I've got the following formula pulling data in from a secon
spreadsheet within the same workbook:

=IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0)

We have 8 different locations ("MICH" being one of them) that we nee
to be able to access. I can write a nested IF formula that looks a
cell I7 (that contains a list of all 8 locations) and, depending o
I7's content, brings back the desired values.

I was hoping someone in the forum could help me write a simpler formul
that would not have 7 IF statements embedded in it.

Any help will be greatly appreciated.

Thanks,
Crist

 

[cparaske]

If anyone has a similar question, the best way to approach it is with
the following formula:
=INDEX(INDIRECT($I$7),MATCH($D7,LOB,0),MATCH($F$5, Month,0))

INDIRECT looks at I7 and uses it as the named range (since this is the
place it occupies in the INDEX statement). There will be an error if I7
does not equal a named range, so you can limit input by having a list
(through data validation)