Automation in Google Sheets

[hj121479]

Hi, I'm trying to build automation for below -

If Name A requested a vacation from 1/20/2023 to 1/24/2023 (C2 and D2), in the below chart, I am hoping to automatically match the dates and "Vacation" wording from Column E2.

Thank you for your help in advance.

 

[Sufiyan97]

What should be your expected result.
can you post some examples?

 

[hj121479]

What should be your expected result.
can you post some examples?

Hi there - here is the example I would like to have. Based on the row 1 -3, I'm hoping to see something like in row 7 and 8

 

[Fluff]

Is this for Google sheets, or Excel?

 

[hj121479]

Is this for Google sheets, or Excel?

It's for Google sheet. Thank you.

 

[Fluff]

In that case you should make that clear in your title (rather than referring to Excel) & such questions should be posted in the General Discussion & Other Applications section of the board.
I have done that for you this time.

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Start Date	End Date	Reason
2			Name A	1/20/2023	1/24/2023	Vacation
3			Name B	1/25/2023	1/29/2023	Off
4			Name C	1/21/2023	1/21/2023	Vacation
5
6			Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
7			1/20/2023	1/21/2023	1/22/2023	1/23/2023	1/24/2023	1/25/2023	1/26/2023	1/27/2023	1/28/2023	1/29/2023	1/30/2023
8		Name A	Vacation	Vacation	Vacation	Vacation	Vacation
9		Name B						Off	Off	Off	Off	Off
10		Name C		Vacation
11
12
13
14
Sheet3

Cell Formulas
Range	Formula
D2:D4	D2	=INDEX($C$7:$M$7,,MATCH(F2,$C8:$M8,0))
E2:E4	E2	=INDEX($C$7:$M$7,,MATCH(MAXIFS($C$7:$M$7,$C8:$M8,F2),$C$7:$M$7,0))
F2:F4	F2	=INDEX($C$8:$M$10,MATCH($C2,$B$8:$B$10,0),MATCH("*",$C8:$M8,0))

 

[hj121479]

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Start Date	End Date	Reason
2			Name A	1/20/2023	1/24/2023	Vacation
3			Name B	1/25/2023	1/29/2023	Off
4			Name C	1/21/2023	1/21/2023	Vacation
5
6			Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
7			1/20/2023	1/21/2023	1/22/2023	1/23/2023	1/24/2023	1/25/2023	1/26/2023	1/27/2023	1/28/2023	1/29/2023	1/30/2023
8		Name A	Vacation	Vacation	Vacation	Vacation	Vacation
9		Name B						Off	Off	Off	Off	Off
10		Name C		Vacation
11
12
13
14
Sheet3

Cell Formulas
Range	Formula
D2:D4	D2	=INDEX($C$7:$M$7,,MATCH(F2,$C8:$M8,0))
E2:E4	E2	=INDEX($C$7:$M$7,,MATCH(MAXIFS($C$7:$M$7,$C8:$M8,F2),$C$7:$M$7,0))
F2:F4	F2	=INDEX($C$8:$M$10,MATCH($C2,$B$8:$B$10,0),MATCH("*",$C8:$M8,0))

Thank you but I'm trying to do it vice verse. From the data you have from C1 to F4, I'm trying to pull a chart in b6 to m10. Would you please help?

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Start Date	End Date	Reason
2			Name A	1/20/2023	1/24/2023	Vacation
3			Name B	1/25/2023	1/29/2023	Off
4			Name C	1/21/2023	1/21/2023	Vacation
5
6			Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
7			1/20/2023	1/21/2023	1/22/2023	1/23/2023	1/24/2023	1/25/2023	1/26/2023	1/27/2023	1/28/2023	1/29/2023
8		Name A	Vacation	Vacation	Vacation	Vacation	Vacation
9		Name B						Off	Off	Off	Off	Off
10		Name C		Vacation
11
12
13
14
15
16
Sheet3 (2)

Cell Formulas
Range	Formula
C7	C7	=MIN($D$2:$D$4)
D7:M7	D7	=IF(C7<MAX($E$2:$E$4),C7+1,"")
C8:M10	C8	=IF(AND(C$7<=INDEX($E$2:$E$4,MATCH($B8,$C$2:$C$4,0)),C$7>=INDEX($D$2:$D$4,MATCH($B8,$C$2:$C$4,0))),INDEX($F$2:$F$4,MATCH($B8,$C$2:$C$4,0)),"")

 

[hj121479]

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Start Date	End Date	Reason
2			Name A	1/20/2023	1/24/2023	Vacation
3			Name B	1/25/2023	1/29/2023	Off
4			Name C	1/21/2023	1/21/2023	Vacation
5
6			Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
7			1/20/2023	1/21/2023	1/22/2023	1/23/2023	1/24/2023	1/25/2023	1/26/2023	1/27/2023	1/28/2023	1/29/2023
8		Name A	Vacation	Vacation	Vacation	Vacation	Vacation
9		Name B						Off	Off	Off	Off	Off
10		Name C		Vacation
11
12
13
14
15
16
Sheet3 (2)

Cell Formulas
Range	Formula
C7	C7	=MIN($D$2:$D$4)
D7:M7	D7	=IF(C7<MAX($E$2:$E$4),C7+1,"")
C8:M10	C8	=IF(AND(C$7<=INDEX($E$2:$E$4,MATCH($B8,$C$2:$C$4,0)),C$7>=INDEX($D$2:$D$4,MATCH($B8,$C$2:$C$4,0))),INDEX($F$2:$F$4,MATCH($B8,$C$2:$C$4,0)),"")

Thank you so much! This is really helpful!

One quick question - If I want to bring the timesheet from a different workbook, how should I use IMPORTRANGE formula to the 'IF(AND(C$7<=INDEX($E$2:$E$4,MATCH($B8,$C$2:$C$4,0)),C$7>=INDEX($D$2:$D$4,MATCH($B8,$C$2:$C$4,0))),INDEX($F$2:$F$4,MATCH($B8,$C$2:$C$4,0)),"")' formula?