So, I have a Gantt chart style diary. I want to apply conditional formatting to any column whose date (in row 2), is a public holiday. There is a sheet called 'Public Holidays', that has the dates of all public holidays in column A.
I found a relevant formula to try from Peter Smulders answer to this topic: How to check if value is in range of cells?
Here's my version:
=NOT(ISERROR(MATCH(B$2:RV$2, 'Public Holidays'!$A:$A,0)))
Nothing happens. It doesn't activate on any cells or columns whether public holidays or not. Weirdly though, if I paste that formula into a bunch of cells, it returns correctly, ie any cell in a column whose date in row 2 is a public holiday returns TRUE
and any cell in a column whose date is not a public holiday returns FALSE
.
I don't understand why it works as a cell formula but not as a conditional formatting formula.
Any help would be greatly appreciated.
Here's a copy of my sheet: https://docs.google.com/spreadsheets/d/1sYI2oDyDR5p5oIJvE_AKIUm_PMReL4nGfXlMU5YvRxI/edit?usp=sharing
B3:NC
orB:NC
?B3:NC
pleaseindirect
if you want to reference other sheets in conditional formatting