Create drop down list based on column header

[surkdidat]

Hi everyone
On the tab "Main" in column A I have a list of peoples names, (A2 downwards), and in row 1 (Column B to Column Z) I have courses names.

In the tab "Course Dates", on I have the course names in Row 1 (Col A to COlumn S)

In each of these columns I have the dates available for each course.

What i need is a dropdown on the "Main" tab, that looks up the name in Row 1, and then the dates are specific for that course.

MAIN TAB

Name (A1)	Course1 (B1)	Course2 (C1)	Course3 (D1)
Peter	(Dropdown for dates in "Course Dates" tab Course1 only
Paul
Lola
Lucy

COURSE DATES TAB

Course1 (A1)	Course2 (B1)	Course3 (C1)	Course4 (D1)
01/01/24	17/01/24	02/02/24	09/03/24
08/01/24	23/02/24	05/02/24	15/03/24
01/02/24	30/03/24	19/02/24	28/04/24
14/02/24	04/04/24	03/03/24	01/05/24

 

[DanteAmor]

Put the following formulas in data validation

varios 06mar2024.xlsm
	A	B	C	D	E
1	Name	Course1	Course2	Course3	Course4
2	Peter
3	Paul
4	Lola
5	Lucy
MAIN

Cells with Data Validation
Cell	Allow	Criteria
B2:B5	List	=INDIRECT("Table2[" & $B1 & "]")
C2:C5	List	=INDIRECT("Table2[" & $C1 & "]")
D2:D5	List	=INDIRECT("Table2[" & $D1 & "]")
E2:E5	List	=INDIRECT("Table2[" & $E1 & "]")

Put the data from sheet "COURSE DATES" into table2.

varios 06mar2024.xlsm
	A	B	C	D
1	Course1	Course2	Course3	Course4
2	01/01/2024	17/01/2024	02/02/2024	09/03/2024
3	08/01/2024	23/02/2024	05/02/2024	15/03/2024
4	01/02/2024	30/03/2024	19/02/2024	28/04/2024
5	14/02/2024	04/04/2024	03/03/2024	01/05/2024
COURSE DATES