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.
