Conditional Formatting Formula help

[energyczar]

Hi, thanks for taking a look at my question.

I'm working to build a visual representation of an employee attendance, I have the calendar and can make it high-lite a date when an occurrence is found. I would like to take it one step further by making the cell of the date high-lite a specific color based on what happened that day.

Tab1 - Calendar
The calendar
You can change the employee name(It was a drop down validation list, but I just added a generic name for uploading)
You can change the year updating the monthly calendars
The dates high lite based on data in Tab 2 (This is currently done with a conditional format {=VLOOKUP(C7,CalcSheet!$D:$D,1)} )

Tab 2 - CalcSheet
Data consolidation
Based on the name entered on Tab 1 the cells look for the name and return back; name, date, occurrence, and a reference cell. The reference cells corresponds with cells on Tab 1 AI column. I figured it would be easier to compare a simple number to words.

Tab 3 - DateSheet
This is the raw data of the call off for the employees, in the upload version I've removed everyone but Employee 1.
Column F in the live version just has a formula to look at column E to turn it into a numerical value.

So here is where I am stuck...
How can I make a conditional formatting with a formula that uses a date on the calendar(Jan 12,2017 as an example), looks for the date on CalcSheet, if found look at Ref Cell to see if it matches the option selected in column AI on the Calendar tab(there would be a conditional formula for each of the options in column AI, so the formula would just be comparing one number) and colors the cell purple. In total there would be 7 formulas to color the cells the correct color, each formula would just do one number comparison.

Sorry I know that's a mouthful, I've been working on this for a day or so and my thoughts are a little all over the place.

Thanks for any help anyone can provide or point me in the right direction. If I can answer any questions please let me know.

[CAntosh]

Welcome to the forum!

If I'm understanding you correctly, then select C7:AG30 and use the following CF Formula:

=AND(VLOOKUP(C7,CalcSheet!$D$3:$F$158,3)=1,INDEX(CalcSheet!$C$3:$C$158,MATCH(C7,CalcSheet!$D$3:$D$158,0))=$A$1)

Format with yellow fill. Repeat the process, replacing "=1" in the formula with =2 and choose red, then repeat with =3 and navy blue, etc. You can shift the order of primacy in "Manage Rules" to set which color shows if there are multiple entries for a date. I've done the first three colors in the attachment. Take a look to see if it'll work for you:

[azumi]

Hope this works............

[energyczar]

Thank you both for the replies and help, my problem is now solved. You all rock!!

[CAntosh]

Glad to help, good luck!