Challenging/Complex named range or Idea

[Masha92]

Hi folks!

Can you please help me with the below?

I have 2 columns

AAA	12/1/2019​
AVC	11/2/2019​
AAF	11/3/2019​
AA	11/4/2019​
AAA	11/5/2019​
AA	11/6/2019​
AA	12/7/2019​
AA	11/21/2019​
AAF	11/15/2019​
AVC	11/11/2019​

I want to define named ranges depending on column A value. For instance, Named Range 1 will include every cells in column B adjacent to AAA. So It will be B1 & B5.
And named range 2 will include all values adjacent to AA. So it will be b4, b6, b7 and B8 and so on.

Is this possible?

The idea is I want to use workday formula and holidays are column B.

Let me know please if this can work and any other suggestions as a workaround.

Thanks a lot )

Masha

 

[pgc01]

Hi Masha

For ex., I defined the named range for "AA" (I assumed your table starts in A1)

I used for the AA holidays: 2019-11-06, 2019-11-07 and 2019-11-21

Name: rangeAA
Refers to: =N(OFFSET(Sheet3!$B$1,SMALL(IF(Sheet3!$A$1:$A$10="AA",ROW(Sheet3!$A$1:$A$10)-ROW(Sheet3!$A$1)),ROW(INDIRECT("1:"&COUNTIF(Sheet3!$A$1:$A$10,"AA")))),0))

To test I used in E1 the start date 2019-11-04, Monday

In D4:D7 I wrote the days to add: 1,2,3,4

Add 1 day and get the 5th, Tuesday
Add 2 days and you should get the 6th if the 6th were a workday. The 6th and the 7th are, however, holidays. I get instead the 8th, Friday.
Add 3 days and you would get the 9th. The 9th is, however a Saturday and the Workday() function leaps over the weekend. This means I get the 11th, Monday
Add 4 day and get the 12th, tuesday.

In E4:
=WORKDAY($E$1,D4,rangeAA)
Copy down

HTH

 

[pgc01]

AAA	2019-12-01​		Date	2019-11-04​
AVC	2019-11-02​
AAF	2019-11-03​		Add days
AAA	2019-11-04​		1​	2019-11-05​	Tuesday
AAA	2019-11-05​		2​	2019-11-08​	Friday
AA	2019-11-06​		3​	2019-11-11​	Monday
AA	2019-11-07​		4​	2019-11-12​	Tuesday
AA	2019-11-21​
AAF	2019-11-15​
AVC	2019-11-11​

 

[Masha92]

Thank you a lot for your effort on this! I really appreciate it!

I tried now your solution but it doesnt work for some reason. I think i applied it incorrectly... is it possible you can share a workfile?

Another thing, when you add 3 days to the 6th of November, you should get 12th of november instead of 11th since the 7th is a holiday. So the formula should count 8th, 11th and 12th. Am i missing something?

Thanks again though
Masha

 

[pgc01]

Another thing, when you add 3 days to the 6th of November, you should get 12th of november instead of 11th since the 7th is a holiday. So the formula should count 8th, 11th and 12th. Am i missing something?

Hi Masha

I'm not adding 3 days to the 6th of November, I'm adding 3 days to the 4th of November.
Check the value in E1

I tried now your solution but it doesnt work for some reason. I think i applied it incorrectly... is it possible you can share a workfile?

I just tried it again and it worked with no problem.
Possible problems:

The named range refers to sheet3. Maybe you have the data in another sheet and you forgot to change all the references to the sheet?

I assumed the dates are excel dates. Maybe you have text dates. Easy to test, for ex. in a cell use =ISNUMBER(B1), you should get TRUE.

 

[jasonb75]

Is there any particular reason for wanting the named ranges? This is how I've done it without them.

Book1
	A	B	C	D	E	F	G
2	AAA	01/12/2019		AA	04/11/2019	1	05/11/2019
3	AVC	02/11/2019		AA	04/11/2019	2	07/11/2019
4	AAF	03/11/2019		AA	04/11/2019	3	08/11/2019
5	AA	04/11/2019		AA	04/11/2019	4	11/11/2019
6	AAA	05/11/2019
7	AA	06/11/2019
8	AA	07/12/2019
9	AA	21/11/2019
10	AAF	15/11/2019
11	AVC	11/11/2019
Sheet9

Cell Formulas
Range	Formula
G2:G5	G2	{=WORKDAY(E2,F2,IF($A$2:$A$11=D2,$B$2:$B$11,E2-1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Note that some of my results are 1 day different to PGC's, his method appears to be adding an extra day to 3 out of the 4 examples, this could be due to the start date being a holiday date.
This is not normal behaviour for the workday function, but if it is something needed here then the formula above will need changing.