Data Validation list question

[jmathias]

I am trying to create a data validation list to populate a list of names into specific calendar dates. The users enter unavailable dates on a second sheet and I want the list to populate on that particular calendar date with only the staff that are available. See screen shots.

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H
1	Sept	Dave	Janet	Johna	Rallie	Annie
2		2023-09-01	2023-09-01	2023-09-08	2023-09-08	2023-09-01
3			2023-09-02	2023-09-09	2023-09-09	2023-09-02
4			2023-09-03	2023-09-10	2023-09-10	2023-09-03
5			2023-09-17	2023-09-20		2023-09-15
6			2023-09-23	2023-09-21		2023-09-16
7			2023-09-30	2023-09-22		2023-09-17
8				2023-09-29
9				2023-09-30
10
11						2023-09-01	2023-09-02
12						Johna Rallie	Dave Johna Rallie
13	2023-09-03	2023-09-08	2023-09-07	2023-09-06	2023-09-05	2023-09-04	2023-09-03
14	Dave Johna Rallie	Dave Janet Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Rallie
15	2023-09-10	2023-09-11	2023-09-12	2023-09-13	2023-09-14	2023-09-15	2023-09-16
16	Dave Janet Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie	Dave Johna Janet Rallie
17	2023-09-17	2023-09-18	2023-09-19	2023-09-20	2023-09-21	2023-09-22	2023-09-23
18	Dave Johna Rallie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Janet Rallie Annie	Dave Janet Rallie Annie	Dave Janet Rallie Annie	Dave Johna Rallie Annie
19	2023-09-24	2023-09-25	2023-09-26	2023-09-27	2023-09-28	2023-09-29	2023-09-30
20	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Johna Janet Rallie Annie	Dave Janet Rallie Annie	Dave Rallie Annie
21
22
23
Sheet1

Cell Formulas
Range	Formula
A12:G12,A22:G22,A20:G20,A18:G18,A16:G16,A14:G14	A12	= IF(ISNUMBER(A11), IF(ISNUMBER(MATCH(A11,Dave,0))=FALSE,"Dave" & CHAR(10),"") &IF(ISNUMBER(MATCH(A11,Johna,0))=FALSE,"Johna" & CHAR(10),"") &IF(ISNUMBER(MATCH(A11,Janet,0))=FALSE,"Janet"& CHAR(10),"") &IF(ISNUMBER(MATCH(A11,Rallie,0))=FALSE,"Rallie"& CHAR(10),"") &IF(ISNUMBER(MATCH(A11,Annie,0))=FALSE,"Annie"& CHAR(10),""),"")

Named Ranges
Name	Refers To	Cells
Annie	=Sheet1!$F$2:$F$9	A22:G22, A20:G20, A18:G18, A16:G16, A14:G14, A12:G12
Dave	=Sheet1!$B$2:$B$9	A22:G22, A20:G20, A18:G18, A16:G16, A14:G14, A12:G12
Janet	=Sheet1!$C$2:$C$9	A22:G22, A20:G20, A18:G18, A16:G16, A14:G14, A12:G12
Johna	=Sheet1!$D$2:$D$9	A22:G22, A20:G20, A18:G18, A16:G16, A14:G14, A12:G12
Rallie	=Sheet1!$E$2:$E$9	A22:G22, A20:G20, A18:G18, A16:G16, A14:G14, A12:G12

 

[jmathias]

This lists who is available, but would it present a dropdown list to select only one?

 

[jmathias]

Also, the Names and dates are on a separate sheet called Availability