I defined tHolidays as a Table (to make it dynamic). Conditional formatting doesn't recognize Tables, and it doesn't work with named ranges either.



The number of calculated columns may be reduced, but I kept every step to make for OP easier to understand the logic. And the number of calculated columns is really moot, OP can hide them, and having several columns with simple formulas may be advantageous compared with one column with super-complex formula.

Instead of OP, I'd define the table on data sheet as Table too. Currently I left it as it was originally, but this means whenever a new rows of data are added (or rows are removed), all range references must be edited in all formulas - which means a lot of unneeded work. (And all conditional formatting rules must be edited too, when OP wants to do all calculations there. And those calculations will get quite complex too with references to several ranges on different sheets for every condition.) With Table defined on data entry sheet and all formulas replaced with Table formulas, the whole workbook will be dynamic. But this means, that there mus be a column in data entry Table, which determines conditional formatting condition for every row.

You might want to check the part about CF not working with named ranges.

I defined a block of 3 numbers and assigned the named range MyNumbers to it. In those cells I typed in 1, 1, and 1.

As an example, I used in CF >> =SUM(MyNumbers)=3

Works okay for me.