Too many IF functions
 

I know there is a limit on how many nested IF functions a formula can have,
but is there a "work-around" for this? Here is my example:

=IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave
tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave tracker
FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave
tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker
FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker
FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker
FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,))))))))

But I need to add in the last 3 months. Looking to compare todays date
(D33) to a specific date in each month ('Leave tracker FY10'!C4) to reveal
the amount of vacation time(tracker FY10'!C5) someone has. The above mess
works for the first 9 months, but to add in the last 3 months it fails,
saying I have too many nested IF's.
Not sure I can use VLOOKUP as the date falls into a range. I'm using Excel
2007.
--
G Hall

Too many IF functions
 

Based on the logic of your formula something like this should work...

Array entered** :

=INDEX('Leave tracker FY10'!C5:N5,MATCH(TRUE,D33<='Leave tracker
FY10'!C4:N4,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"GHall" wrote in message
...
I know there is a limit on how many nested IF functions a formula can have,
but is there a "work-around" for this? Here is my example:

=IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave
tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave
tracker
FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave
tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker
FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker
FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker
FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,))))))))

But I need to add in the last 3 months. Looking to compare todays date
(D33) to a specific date in each month ('Leave tracker FY10'!C4) to reveal
the amount of vacation time(tracker FY10'!C5) someone has. The above mess
works for the first 9 months, but to add in the last 3 months it fails,
saying I have too many nested IF's.
Not sure I can use VLOOKUP as the date falls into a range. I'm using
Excel
2007.
--
G Hall

Too many IF functions
 

Thanks Barry and T. Between the two of you, and a little tweaking, it works
great!
--
Gary Hall


"GHall" wrote:

I know there is a limit on how many nested IF functions a formula can have,
but is there a "work-around" for this? Here is my example:

=IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave
tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave tracker
FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave
tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker
FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker
FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker
FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,))))))))

But I need to add in the last 3 months. Looking to compare todays date
(D33) to a specific date in each month ('Leave tracker FY10'!C4) to reveal
the amount of vacation time(tracker FY10'!C5) someone has. The above mess
works for the first 9 months, but to add in the last 3 months it fails,
saying I have too many nested IF's.
Not sure I can use VLOOKUP as the date falls into a range. I'm using Excel
2007.
--
G Hall

Too many IF functions
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GHall" wrote in message
...
Thanks Barry and T. Between the two of you, and a little tweaking, it
works
great!
--
Gary Hall


"GHall" wrote:

I know there is a limit on how many nested IF functions a formula can
have,
but is there a "work-around" for this? Here is my example:

=IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave
tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave
tracker
FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave
tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker
FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker
FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker
FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,))))))))

But I need to add in the last 3 months. Looking to compare todays date
(D33) to a specific date in each month ('Leave tracker FY10'!C4) to
reveal
the amount of vacation time(tracker FY10'!C5) someone has. The above mess
works for the first 9 months, but to add in the last 3 months it fails,
saying I have too many nested IF's.
Not sure I can use VLOOKUP as the date falls into a range. I'm using
Excel
2007.
--
G Hall