Insert event in calendar according to list

[hpernaf]

Hi everyone!
I am creating a calendar and appointment sheet.
Basically it will be possible to insert up to 4 events on each day of the month.
I would like to list the events in a side list (by date and name of the appointment) and that event should be listed on the respective day in my calendar.

I've already managed to create my dynamic calendar, where the dates change according to the month and year selected in cells Q1 and S1.
However, I have not yet been able to automatically list appointments on the calendar as they are added to my list.

I tried some combinations with the Index function, but was unsuccessful.
Attached is the spreadsheet I am developing

Calendar.xlsx

drive.google.com

Can anybody help me? Is there any way to do this without using matrix formulas?

Thank you in advance.

 

[Toadstool]

HI HPernaf,

Your image and download are not the same sheet.

 

[hpernaf]

HI HPernaf,

Your image and download are not the same sheet.

Oops. Sorry about that.

Follow the correct worksheet.

calendar excel.xlsx

drive.google.com

 

[hpernaf]

Note: This question was also posted at: Insert event in calendar according to list

 

[Toadstool]

HPernaf,

Does this work for you?

Copy of calendar excel.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						January/2020										2020		January
2																	1-jan-20	4
3		Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday
4	29		30		31	New Years Eve	1	Holiday	2		3		4			January		Date	Appointment
5								Hangover								April		01/01/2020	Holiday
6																May		02/01/2020	Appoitmente Test
7																June		12/31/2019	New Years Eve
8	5		6		7		8		9		10		11			February		01/01/2020	Hangover
9																July		01/17/2020	HappyDay
10																March
11																August
12	12		13		14		15		16		17	HappyDay	18			September
13																October
14																November
15																December
16	19		20		21		22		23		24		25
17
18
19
20	26		27		28		29		30		31		1	Appoitmente Test
21
22
23
Plan1 (2)

Cell Formulas
Range	Formula
G1	G1	=S1&"/"&Q1
R2	R2	=DATE($Q$1,MATCH($S$1,$Q$4:$Q$15,0),1)
S2	S2	=WEEKDAY($R$2,1)
B4	B4	=(R2-S2)+1
D4,N20,L20,J20,H20,F20,D20,N16,L16,J16,H16,F16,D16,N12,L12,J12,H12,F12,D12,N8,L8,J8,H8,F8,D8,N4,L4,J4,H4,F4	D4	=B4+1
B8,B20,B16,B12	B8	=N4+1
O4:O23,M4:M23,K4:K23,I4:I23,G4:G23,E4:E23,C4:C23	C4	=IFERROR(IF(MOD(ROW(),4)+1=1,INDEX($T$5:$T$99,MATCH(B4,$S$5:$S$99,0)),INDEX($T$5:$T$99,AGGREGATE(15,6,ROW($S$5:$S$99)-ROW($S$4)/($S$5:$S$99=MAX(B1:B4)),MOD(ROW(),4)+1))),"")

Cells with Data Validation
Cell	Allow	Criteria
S1	List	=$Q$4:$Q$15
Q1	List	2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030

 

[hpernaf]

HPernaf,

Does this work for you?

Copy of calendar excel.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						January/2020										2020		January
2																	1-jan-20	4
3		Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday
4	29		30		31	New Years Eve	1	Holiday	2		3		4			January		Date	Appointment
5								Hangover								April		01/01/2020	Holiday
6																May		02/01/2020	Appoitmente Test
7																June		12/31/2019	New Years Eve
8	5		6		7		8		9		10		11			February		01/01/2020	Hangover
9																July		01/17/2020	HappyDay
10																March
11																August
12	12		13		14		15		16		17	HappyDay	18			September
13																October
14																November
15																December
16	19		20		21		22		23		24		25
17
18
19
20	26		27		28		29		30		31		1	Appoitmente Test
21
22
23
Plan1 (2)

Cell Formulas
Range	Formula
G1	G1	=S1&"/"&Q1
R2	R2	=DATE($Q$1,MATCH($S$1,$Q$4:$Q$15,0),1)
S2	S2	=WEEKDAY($R$2,1)
B4	B4	=(R2-S2)+1
D4,N20,L20,J20,H20,F20,D20,N16,L16,J16,H16,F16,D16,N12,L12,J12,H12,F12,D12,N8,L8,J8,H8,F8,D8,N4,L4,J4,H4,F4	D4	=B4+1
B8,B20,B16,B12	B8	=N4+1
O4:O23,M4:M23,K4:K23,I4:I23,G4:G23,E4:E23,C4:C23	C4	=IFERROR(IF(MOD(ROW(),4)+1=1,INDEX($T$5:$T$99,MATCH(B4,$S$5:$S$99,0)),INDEX($T$5:$T$99,AGGREGATE(15,6,ROW($S$5:$S$99)-ROW($S$4)/($S$5:$S$99=MAX(B1:B4)),MOD(ROW(),4)+1))),"")

Cells with Data Validation
Cell	Allow	Criteria
S1	List	=$Q$4:$Q$15
Q1	List	2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030

Perfect my friend!
Thank you!