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]

Look at here if you can adopt:

How to Use the IMPORTRANGE Google Sheets Function

Use the IMPORTRANGE Google Sheets function to access data between spreadsheets. Follow along for video guidance and advice on the formula.

spreadsheetpoint.com

 

[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, this is helpful. Somehow, I'm having one more issue -

I see Employee J's dates are reflecting correctly but nothing is showing for V and E (I used the same formula for J).

Below are the formula I used -
J: =IF(AND(AR$8<=INDEX('Form Responses 1'!$D:$D,MATCH($B122,'Form Responses 1'!$B:$B,0)),AR$8>=INDEX('Form Responses 1'!$C:$C,MATCH($B122,'Form Responses 1'!$B:$B,0))),INDEX('Form Responses 1'!$E:$E,MATCH($B122,'Form Responses 1'!$B:$B,0)),"")

V: =IF(AND(AR$8<=INDEX('Form Responses 1'!$D:$D,MATCH($B165,'Form Responses 1'!$B:$B,0)),AR$8>=INDEX('Form Responses 1'!$C:$C,MATCH($B165,'Form Responses 1'!$B:$B,0))),INDEX('Form Responses 1'!$E:$E,MATCH($B165,'Form Responses 1'!$B:$B,0)),"")

E: =IF(AND(AR$8<=INDEX('Form Responses 1'!$D:$D,MATCH($B238,'Form Responses 1'!$B:$B,0)),AR$8>=INDEX('Form Responses 1'!$C:$C,MATCH($B238,'Form Responses 1'!$B:$B,0))),INDEX('Form Responses 1'!$E:$E,MATCH($B238,'Form Responses 1'!$B:$B,0)),"")

 

[hj121479]

Look at here if you can adopt:

How to Use the IMPORTRANGE Google Sheets Function

Use the IMPORTRANGE Google Sheets function to access data between spreadsheets. Follow along for video guidance and advice on the formula.

spreadsheetpoint.com

Please disregard my previous post.

If I have multiple dates in multiple rows, it's only picking up the first row. But I need to show all dates in multiple rows. Anyway I can fix this?

Thank you!

 

[Sufiyan97]

Can you post some rows of your data using xl2bb.

 

[hj121479]

Can you post some rows of your data using xl2bb.

Here are the multiple rows with different dates. Anyway I can pull those multiple rows/dates and mark them into the chart? Sorry, I'm not able to install xls2bb on my work computer.

 

[Fluff]

Can you post some rows of your data using xl2bb.

Not using Google Sheets as it does not support VBA.

 

[Sufiyan97]

Hi,
I am also stuck in this, Hope someone elase can help you with it.

Please look into this
@JamesCanale @Peter_SSs

 

[hj121479]

Hi,
I am also stuck in this, Hope someone elase can help you with it.

Please look into this
@JamesCanale @Peter_SSs

No problem, thank you so much for all your help. Would you be able to assist with this? Excel Automation