Conditional Formatting based on table to calendar

Purrple

New Member
Joined
Oct 20, 2022
Messages
5
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Web
Hi all,

For some reason I really can't wrap my head around this bit of conditional formatting and was hoping someone would be able to help me.

Sheet Team Attendance

Attendance Tracker 01.PNG


I have a calendar in this format where the month can be selected by drop down (Ignore that it says select an employee, I forgot to change the text from another variation I tried!). Dates are dynamic and shift accordingly in the grid. Along the side there is a list of employee names.

On a separate sheet I have a table where employees can put their work dates and their status - in office, holiday, leave, etc.

Sheet Employee Tracker

Attendance Tracker 02.PNG


This list is populated into a variable LstEmpWork

There is also a sheet with all the different types of leave in a list that is used to populate the drop down menus. This is variable LstHTypes.

What I'm looking to accomplish is have the initial Sheet (Team Attendance) have the dates highlighted conditionally for each employee based on the Employee Tracker sheet, noting the differences between in office work, vacation, etc.

I was thinking of a COUNTIFS formula but I just couldn't figure out how to get this to reference the listed names against the Employee Tracker and then also have each tracked type of work be filtered as well.

Appreciate any input available!
 

Attachments

  • overview.PNG
    overview.PNG
    34.2 KB · Views: 13

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
it will be easier to help if you post all sheets using XL2BB
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top