Changing named range reference depending on a cell's content

**cparaske** · 07-29-2005, 11:41 AM

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** · 07-29-2005, 02:06 PM

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)

Changing named range reference depending on a cell's content

LinkBack

Thread Tools

Rate This Thread

Display

Changing named range reference depending on a cell's content

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions