If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nested Ifs & NetworkDays Combined Functions
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 -- Dawg House Inc. "We live in it, therefore, we know it!" |
#2
|
|||
|
|||
Nested Ifs & NetworkDays Combined Functions
Replace B20 in your formula with INDIRECT(B20) and you should be good to go...
Test it out in a standalone NETWORKDAYS function and then plug it in your IF statement -- Always provide your feedback... "Dawg House Inc" wrote: 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 -- Dawg House Inc. "We live in it, therefore, we know it!" |
#3
|
|||
|
|||
Nested Ifs & NetworkDays Combined Functions
Also make sure that you have NOT included the country name in the defined
range... "I've named each range under the country header to match to the respective country. " indicates that you are good but Named Range: L1:L11 = "Canada" indicates otherwise... -- Always provide your feedback... "Dawg House Inc" wrote: 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 -- Dawg House Inc. "We live in it, therefore, we know it!" |
#4
|
|||
|
|||
Nested Ifs & NetworkDays Combined Functions
Thanks Sheloo. That indirect will be helpful going forward.
Muchly appreciated. -- Dawg House Inc. "We live in it, therefore, we know it!" "Sheeloo" wrote: Replace B20 in your formula with INDIRECT(B20) and you should be good to go... Test it out in a standalone NETWORKDAYS function and then plug it in your IF statement -- Always provide your feedback... "Dawg House Inc" wrote: 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 -- Dawg House Inc. "We live in it, therefore, we know it!" |
#5
|
|||
|
|||
Nested Ifs & NetworkDays Combined Functions
Hi,
Here is one approach: Suppose you put your different countries in J2:Q18 with the names of the countries in J1:Q1. Also suppose the country name for the D12, E12 data is in F12 Then you can use the following formula: =IF(E12="","",NETWORKDAYS(D12,E12,INDEX(J$2:Q$18,, MATCH(F12,J$1:K$1,0)))-1) Please click yes if this was helpful. -- Thanks, Shane Devenshire "Dawg House Inc" wrote: 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 -- Dawg House Inc. "We live in it, therefore, we know it!" |
Thread Tools | |
Display Modes | |
|
|