Excel Calendar using data from a list.

[LEFROGUE]

Hi All,

I have a list of events that I want to be pulled into a calendar so it can be displayed easily.


The calendar is just a generic Excel template.

Is there a formula that will be able to pull the info from the table and place it into the correct dates on the calendar?

The calendar has a drop-down list for months. Do you think this is an issue? Or would a year calendar be easier for it to pull through?

My Xl2bb is not working on this laptop. If someone needs it I can add it.

Appreciate it thankyou.

 

[JamesCanale]

I don't know how you do your calendar. Does each cell with a day of the month contain the whole date and you are just showing the day?

I made some calendar up and then put schedule items in there from a list.

MrExcelPlayground20.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Country	City	Start	End	Sector	Comments
2	Tech Summit	UK	London	1/2/2024	1/5/2024	IT
3	Medical Conference	SEA	ASST	1/31/2024	2/5/2024	Medicine
4	Insurance Expo	UAE	Dubai	3/16/2024	3/20/2024	Insurance
5	Tolkien Moot	UK	Cambridge	1/5/2024	1/8/2024	Literature
6	Baseball Game	USA	New York	3/17/2024	3/17/2024	Sports
7	Retreat	Canada	Vancouver	2/28/2024	3/2/2024	Well Being
8
9										2024	January
10	January									Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
11	February									01	02	03	04	05	06	07
12	March										Tech Summit UK, London IT	Tech Summit UK, London IT	Tech Summit UK, London IT	Tech Summit UK, London IT Tolkien Moot UK, Cambridge Literature	Tolkien Moot UK, Cambridge Literature	Tolkien Moot UK, Cambridge Literature
13	April									08	09	10	11	12	13	14
14	May									Tolkien Moot UK, Cambridge Literature
15	June									15	16	17	18	19	20	21
16	July
17	August									22	23	24	25	26	27	28
18	September
19	October									29	30	31	01	02	03	04
20	Novemeber											Medical Conference SEA, ASST Medicine	Medical Conference SEA, ASST Medicine	Medical Conference SEA, ASST Medicine	Medical Conference SEA, ASST Medicine	Medical Conference SEA, ASST Medicine
21	December									05	06	07	08	09	10	11
22										Medical Conference SEA, ASST Medicine
Sheet18

Cell Formulas
Range	Formula
K11	K11	=VALUE(L9&" 1, "&K9)-WEEKDAY(VALUE(L9&" 1, "&K9))+2
L11:Q11,L13:Q13,L15:Q15,L17:Q17,L19:Q19,L21:Q21	L11	=K11+1
K12:Q12,K14:Q14,K16:Q16,K18:Q18,K20:Q20,K22:Q22	K12	=LET(v,K11,w,$E$2:$E$7,x,$F$2:$F$7,y,$B$2:$B$7,yy,$C$2:$C$7,yyy,$D$2:$D$7,yyyy,$G$2:$G$7,a,y&CHAR(10)&yy&", "&yyy&CHAR(10)&yyyy,b,(v>=w)*(v<=x),c,IFERROR(TEXTJOIN(CHAR(10)&CHAR(10),TRUE,FILTER(a,b>0)),""),c)
K13,K15,K17,K19,K21	K13	=Q11+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K12:Q12,K14:Q14,K16:Q16,K18:Q18,K20:Q20,K22:Q22	Expression	=MONTH(VALUE($L$9&" 1, "&$K$9))<>MONTH(K11)	text	NO
K11:Q11,K13:Q13,K15:Q15,K17:Q17,K19:Q19,K21:Q21	Expression	=MONTH(VALUE($L$9&" 1, "&$K$9))<>MONTH(K11)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L9	List	=$B$10:$B$21

 

[Lali2023]

Hello,
I have a similar issue but am not sure if the current calendar format would support that. I found the initial calendar format online and it works.

Like the initial poster, I have a separate table template with dates that I want to populate into the calendar. I can expand the size of the calendar, but each cell already has the formula to calculate each date.


The table on the right updates depending on the month/year. Can the info from a separate worksheet still populate the calendar? I am also looking at templates for Power BI. Would that actually be a better choice?