How to get the separate list of OFF Days (Date) of an employee from an attendance sheet

[azad092]

hi

dear members I have a attendance sheet of different employees having different day off.
and I have also an other sheet of day off... I want that when I select a specific date from the dropdown list in the day off sheet it should show the day off status of the that employee whom have off on that date... day off sheet is inserted but attendance sheet could not because of having merge cells. so you can view the image of attendance sheet

Attendance Roster.xlsx
	A	B	C	D	E	F	G
1		Attendance Sheet Motorbike Ambulance Staff
2		Shift	Morning		Date:	5-Nov-21
3	Sr:	Name	MB	Key Points	Attendance	Interchange	Reliever
4
5	1	Kamran Irshad	706	Vehari Chowk
6	2	Junaid Hussain	779
7	3	Muhammad Shehbaz Raza	775	Timber Market
8	4	Numan Nawaz	758
9	5	Faiz Atta	737	BWP Bypass
10	6	Shahid Rasool	713
11	7	M. Imran Fareed	726	Reliever
12	8	Muhammad Zeshan	761	Central station
13	9	Bhai Abdul Wahab	741
14	10	Muhammad Kashif	707	Gulshan Market
15	11	Muhammad Waqar	757
16	12	Muhammad Ramzan	720	NLC Bypass
17	13	Muhammad Asif	740
18	14	Usman Khalil Chohan	710	Reliever
19	15	Syed M. Gull Zaman Shah	788	Northren Bypass
20	16	Altaf Hussain	745
21	17	M. Rizwan Iqbal	703	Chungi No: 9
22	18	Muhammad Amir Farhad	790
23	19	Shehbaz Ali	735	SP Chowk
24	20	Ashar Abbas	754
25	21	Muhammad Hassan	751	Reliever
26	22	Zohaib Hassan	765	Mamtazabad
27	23	Qamar Iqbal	781
28	24	Sajid Iqbal	777	Farooq Pura
29	25	Azhar Aziz	731
30	26	Shakeel Ahmed	774	H M W
31	27	Hafiz Ghulam Shabbir	772
32	28	Muhammad Ramzan	729	Reliever
33	29	Muhammad Mubashir Bukhari		CS
34	30	Muhammad Saleem
35	31	Asad Ur Rahman	791	Dolat Gate
36	32	Junaid Aslam	742	15 Office
37	33	Muhammad Aziz	786	Pull Wasil
38	34	Saddam Ayoub	712	Bastti Malook
39	35	Muhammad Ahad Farhan	764	DC Office
Day OFF

Cell Formulas
Range	Formula
C2	C2	=Attendance!B6
D5,B35:D39,B5:B34,D33,C5:C32,D30,D28,D26,D23,D21,D19,D16,D14,D12,D9,D7	D5	=Attendance!D10

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=Date