Adding details to a calendar in Excel automatically

[Purrple]

Hi folks,

Appreciate any insight you are able to give! I'm sitting here a little bit stumped about the best way to approach this problem, so if you have better ideas than what I've suggested I am all ears!

I have a table that looks a bit like this:

And realistically, I want to have this information on a calendar view.
So I created a dynamic calendar within my spreadsheet that looks like this (and can be modified based on month/year drop downs)



How could I get the name of the person to appear on the calendar with each applicable date including every date in between the first and last?

 

[JamesCanale]

MrExcelPlayground13.xlsx
	A	B	C	D	E	F	G
1	January 2022
2	M	T	W	T	F	S	S
3						1	2
4						John	John
5	3	4	5	6	7	8	9
6	John	John, Janet	John, Janet	Janet	Janet	Janet	Janet
7	10	11	12	13	14	15	16
8	Janet
9	17	18	19	20	21	22	23
10
11	24	25	26	27	28	29	30
12					Pete	Pete	Pete
13	31
14
15
16
17	Name	Start	Finish
18	John	1/1/2022	1/5/2022
19	Janet	1/4/2022	1/10/2022
20	Pete	1/28/2022	1/30/2022
21	Janet	2/1/2022	2/3/2022
22	Tom	2/5/2022	2/7/2022
Sheet11

Cell Formulas
Range	Formula
A3	A3	=IF(WEEKDAY($A$1,2)=COLUMN(A3),$A$1,"")
B3:G3	B3	=IF(WEEKDAY($A$1,2)=COLUMN(B3),$A$1,IF(WEEKDAY($A$1,2)<COLUMN(B3),A3+1,""))
A4:G4,A6:G6,A8:G8,A10:G10,A12:G12,A14:G14	A4	=IFERROR(TEXTJOIN(", ",TRUE,IF(A3<>"",FILTER($A$18:$A$22,($B$18:$B$22<=A3)*($C$18:$C$22>=A3)),"")),"")
A5,A7,A9	A5	=G3+1
B5:G5,B7:G7,B9:G9	B5	=A5+1
A11,A13	A11	=IFERROR(IF(MONTH(G9+1)=MONTH($A$1),G9+1,""),"")
B11:G11,B13:G13	B11	=IFERROR(IF(MONTH(A11+1)=MONTH($A$1),A11+1,""),"")

 

[Purrple]

This is amazing! I really couldn't wrap my head around coming up with a solution but seeing your solution makes it seem so obvious. Thank you so much!

 

[JamesCanale]

I spent most of the time - by far - making the dynamic calendar. I never had a use for that before.

Keep coming on this site and solve people's problems (post the answers or don't) - look how other people solve them. You'll double your skill every few months.

 

[Purrple]

Thank you for your encouragement. I do have a follow up question if that's OK - after getting this to work I realized that it would be nice to have the name followed by a location. I've got an additional column in my sheet that says a location name. How could I refine this? We can just assume this is column D in your own example table

(I'm using a CHAR10 as a delimiter to drop it down a line so that it doesn't get too crowded)

=IFERROR(TEXTJOIN(CHAR(10),TRUE,IF(A3<>"",FILTER($A$18:$A$22,($B$18:$B$22<=A3)*($C$18:$C$22>=A3))&" - "&????,"")),""

 

[Purrple]

Thank you for your encouragement. I do have a follow up question if that's OK - after getting this to work I realized that it would be nice to have the name followed by a location. I've got an additional column in my sheet that says a location name. How could I refine this? We can just assume this is column D in your own example table

(I'm using a CHAR10 as a delimiter to drop it down a line so that it doesn't get too crowded)

=IFERROR(TEXTJOIN(CHAR(10),TRUE,IF(A3<>"",FILTER($A$18:$A$22,($B$18:$B$22<=A3)*($C$18:$C$22>=A3))&" - "&????,"")),""

I realized that I was WAY over thinking this and that I could just filter the location name in the same way back to back... Shows both now