Insert event in calendar according to list

hpernaf

New Member
Joined
Jul 1, 2019
Messages
27
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.
Capturar.PNG


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


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

Thank you in advance.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,169
Office Version
  1. 2016
Platform
  1. Windows
HI HPernaf,

Your image and download are not the same sheet.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,169
Office Version
  1. 2016
Platform
  1. Windows
HPernaf,

Does this work for you?

Copy of calendar excel.xlsx
BCDEFGHIJKLMNOPQRST
1January/20202020January
21-jan-204
3SundayMondayTuesdayWednesdayThursdayFridaySaturday
429 30 31New Years Eve1Holiday2 3 4 JanuaryDateAppointment
5   Hangover   April01/01/2020Holiday
6       May02/01/2020Appoitmente Test
7       June12/31/2019New Years Eve
85 6 7 8 9 10 11 February01/01/2020Hangover
9       July01/17/2020HappyDay
10       March
11       August
1212 13 14 15 16 17HappyDay18 September
13       October
14       November
15       December
1619 20 21 22 23 24 25 
17       
18       
19       
2026 27 28 29 30 31 1Appoitmente Test
21       
22       
23       
Plan1 (2)
Cell Formulas
RangeFormula
G1G1=S1&"/"&Q1
R2R2=DATE($Q$1,MATCH($S$1,$Q$4:$Q$15,0),1)
S2S2=WEEKDAY($R$2,1)
B4B4=(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,F4D4=B4+1
B8,B20,B16,B12B8=N4+1
O4:O23,M4:M23,K4:K23,I4:I23,G4:G23,E4:E23,C4:C23C4=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
CellAllowCriteria
S1List=$Q$4:$Q$15
Q1List2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030
 

hpernaf

New Member
Joined
Jul 1, 2019
Messages
27
HPernaf,

Does this work for you?

Copy of calendar excel.xlsx
BCDEFGHIJKLMNOPQRST
1January/20202020January
21-jan-204
3SundayMondayTuesdayWednesdayThursdayFridaySaturday
429 30 31New Years Eve1Holiday2 3 4 JanuaryDateAppointment
5   Hangover   April01/01/2020Holiday
6       May02/01/2020Appoitmente Test
7       June12/31/2019New Years Eve
85 6 7 8 9 10 11 February01/01/2020Hangover
9       July01/17/2020HappyDay
10       March
11       August
1212 13 14 15 16 17HappyDay18 September
13       October
14       November
15       December
1619 20 21 22 23 24 25 
17       
18       
19       
2026 27 28 29 30 31 1Appoitmente Test
21       
22       
23       
Plan1 (2)
Cell Formulas
RangeFormula
G1G1=S1&"/"&Q1
R2R2=DATE($Q$1,MATCH($S$1,$Q$4:$Q$15,0),1)
S2S2=WEEKDAY($R$2,1)
B4B4=(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,F4D4=B4+1
B8,B20,B16,B12B8=N4+1
O4:O23,M4:M23,K4:K23,I4:I23,G4:G23,E4:E23,C4:C23C4=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
CellAllowCriteria
S1List=$Q$4:$Q$15
Q1List2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030
Perfect my friend!
Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,853
Messages
5,574,670
Members
412,610
Latest member
bluedusty
Top