Data Validation Dependent List based on Criteria

I have a list of employees on one sheet which has data that will for the most part not change. This will be the master list of employees and will be managed from sheet 1. We will add to the list from here as well. The example I provided is small but the list will be 400+ people.
On sheet 2 I have census tract assignments. I wanted to add data validation list so that when i choose the drop down, it will only allow certain names to be chosen based on the criteria.

So on "Sheet2 Cell C2" i need a data validation dependent drop down list that looks to "Sheet1 Colunm F" for a Yes. If this is the only criteria I can do thats ok but what I would also like it to do is than look at all the ones with a "yes" and than base it off the unit name.
So it would be in "Sheet1 Cell C2:C84" It would only look for a Yes and the unit Building
For "Sheet2 D2:D84" It would only look at Yes and Code Enforcement

I know how to do a data validation depentant list with 2 selections but i cannot figure out how to do this one. I would also like to avoid VBA if possible

Thanks for the help

Sorry, I would like to help but the problem description is very opaque as you mix what you can do and cannot do and I am getting lost in this.

What is this supposed to mean: "So it would be in "Sheet1 Cell C2:C84" It would only look for a Yes and the unit Building
For "Sheet2 D2:D84" It would only look at Yes and Code Enforcement"

e.g.: If I choose Yes and Building, then what exactly is supposed to happen?

It also doesn't help that your headings don't provide for clear guidance what is what across sheets, e.g.:
headings on Sheet 1 are: Name ID Unit District Job Type Assigned Census Tract
headings on Sheet 2 are: Tract Number District Building Inspector C&E Inspector CSU Inspector

The only thing I can recognize is District.
For all the others ones I have to guess if e.g. C&E stands for Code Enforcement etc.

Please try to describe you problem more accurately and I will endeavour to provide some input.

In the attached sheet I have guessed what would be the first step in what you're describing:
- I provided a drop-down for all possible (unique) values in Column F on sheet 1 (YES/NO) - you can save the complicated formulas and hard cope Yes,No in the criteria 1 data validation if you're sure that this variable can only have these 2 outcomes
- Based on this choice, a second (dependant) drop-down is created which lists all (unique) values (ignoring blanks) from Column C on sheet 1: BUILDING/CODE ENFORCEMENT if YES - and - LSU/BUILDING/CODE ENFORCEMENT if NO.

Hope this helps.
If I got it wrong what the problem is, please rephrase and redescribe.

Regards

You are correct and I apologize for the messy request, I was running into a meeting before posting.

Sheet 1 will be a list of all employees that work for the department. Some of these employees are assigned census tracts which are areas of the city which they are responsible for. Others are not assigned to census Tracts. Others are also not assigned to a district at all. (Districts are remote locations Only) Sheet 2 will be where we assign census tracts from. 1 census tract will have multiple inspectors assigned to it but only 1 per unit. So for example census tract 1 will have an inspector from building, code enforcement and CSU assigned to it.

what I am hoping for is to have a data validation dependent drop down lists in Sheet2 C2:C84 which will only list the names from Sheet1 Column A if there is a Yes in column F. Additionally it would be helpful to be able to have that same list be further parsed down by looking at the unit on sheet1.

So for example on Sheet2 C2:C84 it would only return names in the drop down list from sheet1 that are marked Yes in column F and building in column C. The word Building will always be static for this coulnm on sheet 2
On Sheet2 D2:D84 it would only have names in the drop down list that are on sheet1 and have a Yes in column F and Code Enforcement in Column C

To further add to my problem the list on sheet1 needs the ability to grow & I'm hoping I will not have to adjust formulas every time I add a name

Hope this helps and I really appreciate your time on this problem & apologize again for the confusion