auto updating calendar with formatting

[corporatenobody]

good day to you all

i am currently working with a planner and calendar view tht i have inherited from a colleague and im sure there is a way to automate updates but i cannot for the life of me figure out how to do it, hence this post

i have 2 sheets in a workbook, the first has a list of unique ids in column a and then columns b - n have headers of landmark events in row 1 with dates populating the cells corresponding to the date and landmark event for each unique id filling out the rest of the table

on sheet 2 i have the list of unique ids again in column 1 but not in the same order and i have the dates for the next few years in row 1. what i want to do is colour code the cells in sheet 2 where the date and unique id intersect depending on which landmark event is happening on that date.

i am currently doing this manually but dates change all the time so its getting to the stage that manual isnt going to cut it as its consuming my life

any help would be gratefully appreciated

 

[BigBeachBananas]

I recommend you upload a sample of your sheet using XL2BB. More people would be inclined to help with something more tangible. Thanks.

 

[corporatenobody]

Hi Big Beach Bananas

i appreciate the suggestion but as my name suggests im a corporate monkey and so have no way to copy the data over. i have to use my personal device to even access this site :/

id	start date	checkpoint 1	checkpoint 2
grt1	01/12/2023	13/12/2023	04/01/2024
BCn34	15/08/2023	29/10/2023	01/12/2023
16375	03/05/2025	07/05/2025	25/05/2025

id	03/05/2025	04/05/2025	05/05/2025	06/05/2025	07/05/2025	08/05/2025	09/05/2025	10/05/2025	11/05/2025
16375	green				Yellow

i have added the 2 tables to try and demonstrate what i am talking about. im sure its easy its just my head is awash with index matches at the minute and i cant figure it out

 

[BigBeachBananas]

How about this?

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	id	start date	checkpoint 1	checkpoint 2		id	3/5/2025	4/5/2025	5/5/2025	6/5/2025	7/5/2025	8/5/2025	9/5/2025	10/5/2025	11/5/2025
2	grt1	1/12/2023	13/12/2023	4/1/2024		grt1
3	BCn34	15/08/2023	29/10/2023	1/12/2023		BCn34
4	16375	3/5/2025	7/5/2025	25/05/2025		16375	green				yellow
Sheet2

Cell Formulas
Range	Formula
G2:O4	G2	=IFNA(CHOOSE(MATCH(G$1,FILTER($A$2:$D$4,$F2=$A$2:$A$4),0),"","green","yellow","red"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:O4	Cell Value	contains "red"	text	NO
G2:O4	Cell Value	contains "yellow"	text	NO
G2:O4	Cell Value	contains "green"	text	NO

 

[corporatenobody]

Big Beach Bananasv you are an absolute star. Thank you so much