Holiday tracker

[pure vito]

Hi All,

I have 2 sheets Data Sheet and Date Data,

Within the data sheet is a list of names and each column after has a date from 01/01/2024 onwards, this sheet contains absences that are marked in different ways per column and name,

I have attached a document with the sheets, with an example of how the data will look,

So what I'm trying to achieve here is in the Date Data sheet is a calendar and a drop down list of names, by selecting the name this should populate the calendar with the absence dates associated with the name,

I have filled in the Date Data sheet with the information that should come through,

This may not be making much sense but I hope the images and document should show what my intention is,

Any help would be greatly apprenticed thanks in advance for your time.

Book 2.xlsm

 

[felixstraube]

Does this work?:

I also uploaded the file here: Book 2.xlsm

I added the year to the sheet in B3 (this of course could be hardcoded into the formula)

Book 2.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
3	2024
4
5	Abdi Guleed
6			January								February								March								April
7			Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun
8			C	C	C	C	C	C	C					H	H	H	H						LE	LE	LE		AA	AA	AA	AA	AA	AA	AA
9			C	C	C	C	C	C	C		H	H	H	H	H	H	H		LE	LE	LE	LE	LE	LE	LE		AA	AA	AA	AA	AA	AA	AA
10			C	C	C	C	C	C	C		H	H	H	H	H	H	H		LE	LE	LE	LE	LE	LE	LE		AA	AA	AA	AA	AA	AA	AA
11			C	C	C	C	C	C	C		H	H	H	H	H	H	H		LE	LE	LE	LE	LE	LE	LE		AA	AA	AA	AA	AA	AA	AA
12			C	C	C						H	H	H	H					LE	LE	LE	LE	LE	LE	LE		AA	AA
13
14
15			May								June								July								August
16			Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun
17					T	T	T	T	T							H	H		C	C	C	C	C	C	C					T	T	T	T
18			T	T	T	T	T	T	T		H	H	H	H	H	H	H		C	C	C	C	C	C	C		T	T	T	T	T	T	T
19			T	T	T	T	T	T	T		H	H	H	H	H	H	H		C	C	C	C	C	C	C		T	T	T	T	T	T	T
20			T	T	T	T	T	T	T		H	H	H	H	H	H	H		C	C	C	C	C	C	C		T	T	T	T	T	T	T
21			T	T	T	T	T				H	H	H	H	H	H	H		C	C	C						T	T	T	T	T	T
22
23
24			September								October								November								December
25			Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun
26									C			UA	UA	UA	UA	UA	UA						C	C	C								LT
27			C	C	C	C	C	C	C		UA	UA	UA	UA	UA	UA	UA		C	C	C	C	C	C	C		LT	LT	LT	LT	LT	LT	LT
28			C	C	C	C	C	C	C		UA	UA	UA	UA	UA	UA	UA		C	C	C	C	C	C	C		LT	LT	LT	LT	LT	LT	LT
29			C	C	C	C	C	C	C		UA	UA	UA	UA	UA	UA	UA		C	C	C	C	C	C	C		LT	LT	LT	LT	LT	LT	LT
30			C	C	C	C	C	C	C		UA	UA	UA	UA					C	C	C	C	C	C			LT	LT	LT	LT	LT	LT	LT
31			C																								LT	LT
Date Data

Cell Formulas
Range	Formula
D8:J13,AB26:AH31,T26:Z31,L26:R31,D26:J31,AB17:AH22,T17:Z22,L17:R22,D17:J22,AB8:AH13,T8:Z13,L8:R13	D8	=LET( month,INT((COLUMN()-COLUMN($D$6))/8)+1+INT((ROW()-ROW($D$6))/9)*4, firstDayOfMonth, DATE($B$3,month,1), firstMonday,firstDayOfMonth-WEEKDAY(firstDayOfMonth,3), auxDaysOfMonth,SEQUENCE(37,,firstMonday), daysOfMonth,IF(MONTH(auxDaysOfMonth)=month,auxDaysOfMonth,""), rawAbsenseData,'Data Sheet'!$C$10:$ND$439, absenseData,LEFT(rawAbsenseData,IFERROR(FIND(" ",rawAbsenseData)-1,LEN(rawAbsenseData))), daysData,IFERROR(INDEX(absenseData,MATCH($B$5,'Data Sheet'!$B$10:$B$439,0),MATCH(daysOfMonth,'Data Sheet'!$C$9:$ND$9,0)),""), WRAPROWS(daysData,7,"") )
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B5	List	='Data Sheet'!$B$10:$B$210

 

[Fluff]

Another option

Book 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1
2
3
4
5		Raja Rehman
6				January								February								March								April
7				Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun
8				H	H	H	H	H
9				LE	LE	LT	LT	T
10				UA	AA	C
11
12
13
Date Data

Cell Formulas
Range	Formula
D8:J12	D8	=LET(m,D6,f,FILTER('Data Sheet'!C10:ND210,'Data Sheet'!B10:B210=B5),af,FILTER(TEXTBEFORE(f&" "," "),TEXT('Data Sheet'!C9:ND9,"mmmm")=m),d,WEEKDAY(DATE(YEAR('Data Sheet'!C9),MONTH("1/"&m),1),2),WRAPROWS(DROP(IF(d>1,HSTACK(EXPAND("",,d-1,""),af),af),,-d+1),7,""))
L8:R12	L8	=LET(m,L6,f,FILTER('Data Sheet'!C10:ND210,'Data Sheet'!B10:B210=B5),af,FILTER(TEXTBEFORE(f&" "," "),TEXT('Data Sheet'!C9:ND9,"mmmm")=m),d,WEEKDAY(DATE(YEAR('Data Sheet'!C9),MONTH("1/"&m),1),2),WRAPROWS(DROP(IF(d>1,HSTACK(EXPAND("",,d-1,""),af),af),,-d+1),7,""))
T8:Z12	T8	=LET(m,T6,f,FILTER('Data Sheet'!C10:ND210,'Data Sheet'!B10:B210=B5),af,FILTER(TEXTBEFORE(f&" "," "),TEXT('Data Sheet'!C9:ND9,"mmmm")=m),d,WEEKDAY(DATE(YEAR('Data Sheet'!C9),MONTH("1/"&m),1),2),WRAPROWS(DROP(IF(d>1,HSTACK(EXPAND("",,d-1,""),af),af),,-d+1),7,""))
AB8:AH12	AB8	=LET(m,AB6,f,FILTER('Data Sheet'!C10:ND210,'Data Sheet'!B10:B210=B5),af,FILTER(TEXTBEFORE(f&" "," "),TEXT('Data Sheet'!C9:ND9,"mmmm")=m),d,WEEKDAY(DATE(YEAR('Data Sheet'!C9),MONTH("1/"&m),1),2),WRAPROWS(DROP(IF(d>1,HSTACK(EXPAND("",,d-1,""),af),af),,-d+1),7,""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B5	List	='Data Sheet'!$B$10:$B$210

 

[pure vito]

Thank you both very much for taking the time out to complete this task I honestly didn't expect the solution to be so complex in terms of formula but both nailed it but I have gone with the first response I did actually manage a solution last night by simply using a Vlookup this is as far as my knowledge extends the problem then was conditional formatting but with your solutions you have also solved that problem for me to thanks again your knowledge is always appreciated hat's off

This was the end goal it works perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback.