require more than 7 nested IF statments

[dwebb]

I am preparing a massive year-end report that pulls data from multiple
spreadsheets. Anyway, I have the links in place fine, but I need to be
able to look at data for any month within the year. I have a cell
where I enter the month I'm interested in and I then have hundreds of
fields that I want data populated, based on the month chosen - this is
why I need to have 12 IF statments.

I was able to do this in a single cell by creating 2 criteria
statements, effectively breaking my IF statement in half. However,
since I want use this type of nested statement in hundreds of fields, I
don't want to have to create hundreds of these criteria.

I want a statement like this (obviously this if simplified):
IF(A1="January",B37,IF(A1="February",D12,IF(A1="March".... etc.....
through to December

The Microsoft help page said I can get around the 7 nested IF statement
limitation by creating a 'function macro', but did not provide any
guidance on how exactly to go about doing this! ugghh Can I program a
macro that can then be copied into multiple cells with the target cells
changed? ie: one cell will point to B37 if January is chosen in A1
(per above), but other cells will point to different cells (not B37) if
January is chosen in A1.

If there wasn't a limitation to the # of nested IF statements, or if
the maximum was at least 11, then I'd have no problem.

Thanks in advance to anyone who can make my life easy!

 

[Niek Otten]

Look here:

http://www.j-walk.com/ss/excel/usertips/tip080.htm


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am preparing a massive year-end report that pulls data from multiple
| spreadsheets. Anyway, I have the links in place fine, but I need to be
| able to look at data for any month within the year. I have a cell
| where I enter the month I'm interested in and I then have hundreds of
| fields that I want data populated, based on the month chosen - this is
| why I need to have 12 IF statments.
|
| I was able to do this in a single cell by creating 2 criteria
| statements, effectively breaking my IF statement in half. However,
| since I want use this type of nested statement in hundreds of fields, I
| don't want to have to create hundreds of these criteria.
|
| I want a statement like this (obviously this if simplified):
| IF(A1="January",B37,IF(A1="February",D12,IF(A1="March".... etc.....
| through to December
|
| The Microsoft help page said I can get around the 7 nested IF statement
| limitation by creating a 'function macro', but did not provide any
| guidance on how exactly to go about doing this! ugghh Can I program a
| macro that can then be copied into multiple cells with the target cells
| changed? ie: one cell will point to B37 if January is chosen in A1
| (per above), but other cells will point to different cells (not B37) if
| January is chosen in A1.
|
| If there wasn't a limitation to the # of nested IF statements, or if
| the maximum was at least 11, then I'd have no problem.
|
| Thanks in advance to anyone who can make my life easy!
|

 

[Don Guillett]

try this idea or look in the help index for LOOKUP where is must be in
sorted order.
=VLOOKUP(LEFT(B1,3),{"jan",1;"feb",2;"mar",3},2,0)

 

[Dana DeLouis]

=CHOOSE(MATCH(A1,{"January","February",....

A slight variation on this theme might be something like this...

=CHOOSE(MONTH(A1 & " 1"),D1,D2,D3...)

Month is used to return a number 1-12, "assuming" A1 has text like January,
Jan, etc.