Ok I have a 2-fold problem that is basically the same thing. I'm building a standard monthly Calendar in an Excel sheet for tracking attendance. The layout is the same as you would see in Outlook, where it has 6 rows and 6 columns. I have the calendar setup and it accurately alters the dates based on the month which is selected from a Data Validation list.
I also have another sheet that tracks 3 pieces of data for attendance, employee's name, the attendance 'type' and the date it occurred.
Now my task is to setup Conditional Formatting based on 2 types of criteria.
Firstly, I want to setup formatting to "gray out" dates that don't belong to the currently selected month.
I setup a Conditional formula as such:
=MONTH(B9)<>MONTH(DATEVALUE(D5&"1"))
Where the calendar range is named as 'Calendar' and is a range from B9:H14.
D5 is the cell that contains the currently selected month which I then convert in the formula into it's numerical value to compare it with the value returned from the calendar cell.
This works, for the first cell B9. But it does not cause highlighting on any other cells within the range that match the criteria.
Secondly, I want to setup Conditional Formatting for each calendar cell to reflect based on the attendance Type.
In the calendar sheet I have a Data Validation list that allows the selection of a specific employee. Thusly, the sheet reflects the attendance of a specific employee for a specific month.
Again, using the named Calendar range B9:H14. I want to set it up so its searching the attendance list on the second sheet and highlights the days on the Calendar based on the employee name, the date it occurred and the type.
So for instance if there is an entry in the Attendance List, that John Doe was Sick on 1/2/2018. Then on the calendar it will highlight the cell with the date 1/2/2018 in red if the employee selected in the Data Validation list cell D4 is John Doe.
FYI: The Attendance List sheet is laid out in this order:
Name
Type
Date
And all three columns are named in order: TrackNames, TrackTypes, TrackDates.
I was looking at something along the lines of:
=COUNTIFS(TrackNames, D4, TrackDates, B9, TrackTypes, Data!$D$4)>0
Which again, does work, but it only works on cell B9. Not on the entire range of cells.
MONTH($B$9)? – urdearboy