Changing named range reference depending on a cell's content
 

Where to start?!

I've got the following formula pulling data in from a second
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 need
to be able to access. I can write a nested IF formula that looks at
cell I7 (that contains a list of all 8 locations) and, depending on
I7's content, brings back the desired values.

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

Any help will be greatly appreciated.

Thanks,
Cristo


--
cparaske
------------------------------------------------------------------------
cparaske's Profile: http://www.excelforum.com/member.php...fo&userid=3793
View this thread: http://www.excelforum.com/showthread...hreadid=391309

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)


--
cparaske
------------------------------------------------------------------------
cparaske's Profile: http://www.excelforum.com/member.php...fo&userid=3793
View this thread: http://www.excelforum.com/showthread...hreadid=391309