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="Ma rch".... 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!

Look he

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ps.com...
|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="Ma rch".... 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!
|

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)

--
Don Guillett
SalesAid Software

wrote in message
ps.com...
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="Ma rch".... 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!

=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.

--
Dana DeLouis
Windows XP & Office 2003


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
One more (or two, depending on how you look at it)

=CHOOSE(MATCH(A1,{"January","February","March","Ap ril","May","June","July","August","September","Oct ober","November","December"},0),1,2,3,4,5,6,7,8,9, 10,11,12)

where the numbers 1,2,3,4,...,12) would be the cell reference for each
month's match.

A modification of this would presume that a list of the months exists
somewhere on the sheet (B1 through B12 for this example) - they could be
in a
hidden column somewhere if you like.

=CHOOSE(MATCH(A1,B1:B12,0),"jan","feb","Mar","Apr" ,"May","Jun","Jul","Aug","Sep","Oct","Nov","Dec ")
in this example, your cell references would replace the 3-letter
abbreviations (and not have "" around them.

The second way adds a little bonus: if you set cell A1 to use Data
Validation with the list from B1:B12 (or where ever on the sheet) as the
source for the list, then you're guaranteed that the people have to choose
a
valid choice in A1.


" wrote:

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="Ma rch".... 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!