Greetings, new guy here. Hope the following is a clear and adequate explanation. We've been working on a calendar application which tracks days off, sick days, business trips. It's intended to populate an activities listing containing due dates so we can see what's due and who's around to do it. We've run into a wall despite some local wizards having a go and sure hope someone can assist. The problem involves an IF statement which seems to be limited in the number of rows or cells it can handle. We're not committed to the IF statement, but don't know of another way to handle it. Spreadsheet structure:
Col Usage
A Employee Names with the need for 15
blank cells below between each employee.
B First Date out of Office (MM/DD/YY) for
employee in Col A
C Last Date out of Office (MM/DD/YY).
So, for each employee in column A, they
list the days they will be out of the
office in cols B & C with 16 rows for
them to work with.
D Number of Days out of Office (Calen Days)
E Absence Code (S=Sick,V=Vac,T=Trip)
F Company_Holidays (MM/DD/YY) Each day is
listed in this column.
G Days of the Year (Jan 1-Dec 31 MM/DD/YY)
H Employee #1
I Employee #2 (15 rows down or so)
J Employee #3 and so on
Beginning in column H (I, J etc), we want all the cells populated with one of the following for each calendar day in Col G:
1. If it's a non-business day, an "N"
2. If it's a business day, but the employee
is not in, the code shown in Col E.
3. Otherwise blank
We can then count the results of the formula to track days vacation etc.
The problem we're experiencing is that we can't get the formula to accept any more than five rows i.e. pairs of Dates Out of Office (or perhaps it's the corresponding total number of cells in the formula). When we try to add an additional pair of Dates Out of Office e.g. B13, C13, and E13 (for Absence Code) to the following formula, it ceases to work. There seems to be a limit in the structure of the formula. We'd sure hate to curtail everyone's vacation because of an Excel formula problem, but you know cost containment....
Formula we have at the moment:
=IF(OR(WEEKDAY($G$8,1)=1,WEEKDAY($G$8,1)=7, IF(ISNA(MATCH($G$8,COMPANY_HOLIDAYS,0)),
FALSE,TRUE)),"N",IF(ISERROR(HOLIDAY($G$8,$B$8,$C$8,$B$9,$C$9,$B$10,$C$10,$B$11,
$C$11,$B$12,$C$12,"",$E$8,$E$9,$E$10,$E$11,$E$12)),"",HOLIDAY($G$8,$B$8,$C$8,$B$9,$C$9,
$B$10,$C$10,$B$11,$C$11,$B$12,$C$12,"",$E$8,$E$9,$E$10,$E$11,$E$12)))
Beginning of formula establishes Sat and Sun and Holidays as Not Worked "N", but it gets fuzzy from there. Formula does work for the indicated cells. Any and all input welcome. Many thanks and all the best.
Col Usage
A Employee Names with the need for 15
blank cells below between each employee.
B First Date out of Office (MM/DD/YY) for
employee in Col A
C Last Date out of Office (MM/DD/YY).
So, for each employee in column A, they
list the days they will be out of the
office in cols B & C with 16 rows for
them to work with.
D Number of Days out of Office (Calen Days)
E Absence Code (S=Sick,V=Vac,T=Trip)
F Company_Holidays (MM/DD/YY) Each day is
listed in this column.
G Days of the Year (Jan 1-Dec 31 MM/DD/YY)
H Employee #1
I Employee #2 (15 rows down or so)
J Employee #3 and so on
Beginning in column H (I, J etc), we want all the cells populated with one of the following for each calendar day in Col G:
1. If it's a non-business day, an "N"
2. If it's a business day, but the employee
is not in, the code shown in Col E.
3. Otherwise blank
We can then count the results of the formula to track days vacation etc.
The problem we're experiencing is that we can't get the formula to accept any more than five rows i.e. pairs of Dates Out of Office (or perhaps it's the corresponding total number of cells in the formula). When we try to add an additional pair of Dates Out of Office e.g. B13, C13, and E13 (for Absence Code) to the following formula, it ceases to work. There seems to be a limit in the structure of the formula. We'd sure hate to curtail everyone's vacation because of an Excel formula problem, but you know cost containment....
Formula we have at the moment:
=IF(OR(WEEKDAY($G$8,1)=1,WEEKDAY($G$8,1)=7, IF(ISNA(MATCH($G$8,COMPANY_HOLIDAYS,0)),
FALSE,TRUE)),"N",IF(ISERROR(HOLIDAY($G$8,$B$8,$C$8,$B$9,$C$9,$B$10,$C$10,$B$11,
$C$11,$B$12,$C$12,"",$E$8,$E$9,$E$10,$E$11,$E$12)),"",HOLIDAY($G$8,$B$8,$C$8,$B$9,$C$9,
$B$10,$C$10,$B$11,$C$11,$B$12,$C$12,"",$E$8,$E$9,$E$10,$E$11,$E$12)))
Beginning of formula establishes Sat and Sun and Holidays as Not Worked "N", but it gets fuzzy from there. Formula does work for the indicated cells. Any and all input welcome. Many thanks and all the best.
. What are you trying to do in those sections? Either way, the fact that you seem reduced to referencing all of the cells long-hand rather than refering to a range seems to indicate that there's probably a better way to do it. 
