Conditional Formatting Reference Multiple Cells?

[jtakajr]

Hello,

I am having trouble figuring out if something that would help me very much is possible.

Every day I get a list of data that list the clock-in times for all employees. I'd like to set conditional formatting to identify which shift the employee works on. I'd like conditional formatting to reference a list of day shift employees, and night shift employees and format accordingly.


Sheets in attached excel document:
DESIRED OUTCOME: What I'd like the Data - 8.9.19 sheet to look like after conditional formatting (an example).
Employees by Shift: Contains two columns Day Shift & Night Shift and list all the employees assigned to that shift.
Data - 8.9.19: Clock-in time for the specified date and employee name.

Does anyone have any guidance?

I know I can go in and add a conditional formatting rule for each individual employee, but there are over 150 employees in total and shifts often change, so it would be much easier to update the Employees by Shift page as that information is readily available.

[63falcondude]

Highlight A2:A7 (of the Data worksheet) > Conditional Formatting > New Rule> Use a formula
=COUNTIF('Employees by Shift'!A:A,A2)
Format: Fill yellow > OK > OK

Highlight A2:A7 (of the Data worksheet) > Conditional Formatting > New Rule> Use a formula
=COUNTIF('Employees by Shift'!C:C,A2)
Format: Fill blue > OK > OK