Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Dynamic named range across multiple sheets | Excel Discussion (Misc queries) | |||
macro help | Excel Discussion (Misc queries) | |||
changing the value of each cell in a range by a certain percentage | Excel Discussion (Misc queries) | |||
named range refers to: in a chart | Excel Discussion (Misc queries) |