D
Dawg House Inc
OK...I've tried consulting many boards to get around the nested if limit in
Excel 2003. I remember seeing something about concatenating strings together,
but I've come to the realization that I'm being lazy.
So, here's what I'm trying to do:
I have a list of 20 people who have entered dates for their holidays.
These 20 people are from one of 8 different countries.
Each country has its own "holidays".
What I've attempted to do (and it works without concern for Holidays) is to
take subtract the number of NETWORKDAYS from the StartDate to the EndDate
(ex. =IF(ISBLANK(E12),"",(NETWORKDAYS(D12,E12,0)- 1))). This works just fine
- again, with complete disregard to holidays.
So, I created a Holidays table that consists of 8 columns. Each column
header is the country name. Under each country is a varying number of dates
(ranging from 8-17 dates). I've named each range under the country header to
match to the respective country.
This is when I thought I had it beat and I tried the following change to my
formula:
=IF(ISBLANK(E12),"",(NETWORKDAYS(D12,E12,B20)- 1))
- where B20 is the Country name for the individual.
This results in a #VALUE! error.
For more info...here's the sample sheet info:
A B C D E
1 Name Locale Start EndDate WorkingDaysGone
2 John UK 10-nov-08 12-nov-08 <formula>
For the "Country holidays" i created the following
L M
1 Canada US
2 01-Jan-08 01-Jan-08
3 21-Mar-08 21-Jan-08
4 19-May-08 26-May-08
5 01-Jul-08 04-Jul-08
6 04-Aug-08 01-Sep-08
7 01-Sep-08 13-Oct-08
8 13-Oct-08 27-Nov-08
9 11-Nov-08 25-Dec-08
10 25-Dec-08
11 26-Dec-08
Named Range: L1:L11 = "Canada"
Named Range: M1:M9 = "US"
(there are actually 8 columns in this section)
That setup...I tried to pump this formula into E2:
=IF(ISBLANK(D2),"",(NETWORKDAYS(C2,D12,B2)- 1))
I'm trying to pass the value of the Locale into the function to "act as" the
named range. It does not work.
Does anyone have any suggestions on this??
Thanks in advance.
JCH
Excel 2003. I remember seeing something about concatenating strings together,
but I've come to the realization that I'm being lazy.
So, here's what I'm trying to do:
I have a list of 20 people who have entered dates for their holidays.
These 20 people are from one of 8 different countries.
Each country has its own "holidays".
What I've attempted to do (and it works without concern for Holidays) is to
take subtract the number of NETWORKDAYS from the StartDate to the EndDate
(ex. =IF(ISBLANK(E12),"",(NETWORKDAYS(D12,E12,0)- 1))). This works just fine
- again, with complete disregard to holidays.
So, I created a Holidays table that consists of 8 columns. Each column
header is the country name. Under each country is a varying number of dates
(ranging from 8-17 dates). I've named each range under the country header to
match to the respective country.
This is when I thought I had it beat and I tried the following change to my
formula:
=IF(ISBLANK(E12),"",(NETWORKDAYS(D12,E12,B20)- 1))
- where B20 is the Country name for the individual.
This results in a #VALUE! error.
For more info...here's the sample sheet info:
A B C D E
1 Name Locale Start EndDate WorkingDaysGone
2 John UK 10-nov-08 12-nov-08 <formula>
For the "Country holidays" i created the following
L M
1 Canada US
2 01-Jan-08 01-Jan-08
3 21-Mar-08 21-Jan-08
4 19-May-08 26-May-08
5 01-Jul-08 04-Jul-08
6 04-Aug-08 01-Sep-08
7 01-Sep-08 13-Oct-08
8 13-Oct-08 27-Nov-08
9 11-Nov-08 25-Dec-08
10 25-Dec-08
11 26-Dec-08
Named Range: L1:L11 = "Canada"
Named Range: M1:M9 = "US"
(there are actually 8 columns in this section)
That setup...I tried to pump this formula into E2:
=IF(ISBLANK(D2),"",(NETWORKDAYS(C2,D12,B2)- 1))
I'm trying to pass the value of the Locale into the function to "act as" the
named range. It does not work.
Does anyone have any suggestions on this??
Thanks in advance.
JCH