I'm not sure if this is possible to do, in the way I'm trying to do it, but I'm trying to do Conditional Data Validation with Multiple Conditions without making a Table separate from what the Personnel Roster already provides.

This is a multiple page document:

I have a "Roster" page where :

A	B
Name	Pay Grade
A	E1
B	E2
C	E3
D	E4
E	E5
F	E6
G	E7
H	O1
I	O2
J	O3
K	W1
L	W2
M	W3

I have an "Allocation" page that has monthly duty positions that need to be filled.

Position	Date	Name
Bldg 1-OOD
Bldg 1-AOOD
Bldg 1-DD
Bldg 2-DNCO
Bldg 2-ADNCO
Bldg 2-ROVER 1
Bldg 2-ROVER 2
Bldg 3-DNCO
Bldg 3-ADNCO
Bldg 3-ROVER 1
Bldg 3-ROVER 2
CACO

Is it possible to, with only the information, as is, on the "Roster" page, create a conditional Data Validation List that: if Position cell A2=Bldg 1-OOD, then the data validation list in Cell D2 only pulls names who have the correct rank from the full Roster Column A?

Positions Pay Grades eligible for those positions

Bldg 1-OOD	E6, E7, O1, O2, O3, W1, W2, W3
Bldg 1-AOOD	E4, E5
Bldg 1-DD	E1, E2, E3 (Additional requirement of having a license)
Bldg 2-DNCO	E4, E5
Bldg 2-ADNCO	E1, E2, E3
Bldg 2-ROVER 1	E1, E2, E3
Bldg 2-ROVER 2	E1, E2, E3
Bldg 3-DNCO	E4, E5
Bldg 3-ADNCO	E1, E2, E3
Bldg 3-ROVER 1	E1, E2, E3
Bldg 3-ROVER 2	E1, E2, E3
CACO	E6, E7, O1, O2, O3, W1, W2, W3

Forum Discussion

Conditional Data Validation with multiple conditions