Conditional Formatting help

Occasional Visitor

I am building an attendance tracker.  I am looking to have conditional formatting set up within my calendar where once an employee is selected from the drop down menu (valSelEmployee) it references all inputs for that employee's leave start dates, end dates, leave types and Stat holidays.

For example: Employee 1 is on vacation from April 4, 2022 to April 10, 2022.  When employee #1 is selected from the "drop down" the calendar will be highlighted in Yellow from April 4 to 10, not including weekends.


lstEmpName = list of employee's name

valSelEmployee = employee selected from "drop Down"

lstSdate = list of Start date

lstEdate = list of end date

lstHolidays = list of Stat Holidays

LeaveTypes = the leave type 


Here is the formula i am working off of but I am getting a #VALUE error

=COUNTIFS(lstEmpName,valSelEmployee,lstSdate,"<="&D4,lstEdate,">="&B4,lstHolidays,'Leave Types'!$B$12)>0


I can share the document if it would be helpful.  Here is a screenshot  of the calendar, drop down to select employees.Capture.PNG

0 Replies