Conditional Data Validation with multiple conditions

[JPDeaton]

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

[#2]

@JPDeaton 

It is not so easy to understand your table structure and which cell should now be formatted under which condition. Therefore, here is a not so detailed answer. Yes, it is possible to apply several conditions to one cell. You have to link the conditions with AAND() or OR(). For example like this:

A1=AND(b1=1,c1=2)

At the end, TRUE must come out to trigger your formatting. If FALSE comes out, formatting will not take place.

If you can't do anything with this information, then I'm sorry. You would then have to explain your problem better. It would be best to upload a sample document with non-sensitive data.

[#3]

@JPDeaton 

I didn't really understand what you want to do exactly, but here is an example of a dropdown in dependency.

This might help you in your project.

Would also recommend, with your permission, to be as specific as possible about what the digital environment is.

Which Excel version, which operating system, which storage medium.

In addition, a file (without sensitive data) or photos would also help :).

Hope I was able to help you with this information & file.

NikolinoDE

I know I don't know anything (Socrates)

[#4]

@NikolinoDE 

I honestly tried to upload pictures, and even the file itself, but keep getting told I don't have permissions.

[#5]

I've been trying to do that but the site won't let me upload anything. Keeps telling me I don't have permissions.

But I'll try to explain it better:
I'm trying to make the roster, in question, as easy to use as possible. One of the biggest issues I'm running into is that the assignments for each company are housed on a multiple different pages and that frequently causes a them to lose track of who is where and people get put on there multiple times. I'm trying to consolidate each company's allocation list to one page per company and use dropdowns to make things more uniform.

As it stands, I have the roster to the point where all they have to do is select a name from a dropdown and all of the other information is filled in.

The reason this isn't a viable option, at the moment, is because they have to select from, depending on the company, upwards of 400 names. What I'm trying to do is add a condition to the Detail List so that the list will only generate the names of the people who are eligible to fill that spot, but not be locked to a specific cell in the column.

I listed all of the positions and the ranks that are eligible to fill them. All I need help with is figuring out exactly how to input it into Detail List. If it requires making a new page, then so be it but I'd like to avoid that.

I am more than willing to post the document, after clearing out all of the personnel information from it, but board won't let me add pictures or files.

[#6]

If my agency profile will allow it, I would be more than happy to speak with you guys through TEAMS, if you send me your e-mail

[#7]

I am trying to avoid using a table like the one in your example. But if it's an absolute necessity, then I guess I can try to figure it out.

[#8]

@JPDeaton 

Hi Justin,

Your new explanation sounds like you are getting somewhere with the FILTER() function.

Here you can filter out rows from a range of cells with one or more conditions.

Have a look at the help page for this.

By the way: If you are not allowed to upload files here, you can also use any other file hoster and post the link here.