SOLVED

Conditional Formatting Using an entire Column

Copper Contributor

Hello, 

 

I have a list of dates on one sheet, for example:

12/2/2023

1/6/2024

1/20/2024

2/19/2024

3/5/2024

 

and a list of Holidays and other exclusion dates in another sheet:

12/25/2023
1/1/2024
1/15/2024
2/19/2024
5/27/2024
6/19/2024

 

I'd like to have the first sheet of random dates to be formatted so that if it matches with one of the dates on the second, Holiday/Exclusion sheet, it turns a specific color. 

 

Is there a way to create a conditional format rule using the entire second sheet Holiday column? I am trying to avoid entering the rules individually for each Holiday/Exclusion date as there are a lot. I have tried A:A or A1:A80 and get the error "Change the reference to a single cell, or use the reference with a worksheet function, such as =SUM(A1:E5)".

 

In this case, the rule should format the 2/19/2024 date in the first sheet since it is listed as a holiday in the second sheet.

 

Thank you.

 

5 Replies
try =ISNUMBER(XMATCH(A1, holidays))

@mtarler 

As a comment, ISNUMBER is not required here.

@mtarler I tried incorporating that, but it did not work

best response confirmed by dlopez20 (Copper Contributor)

@dlopez20 not sure why. In the attached I give an example show it works and since I included a fun/handy HOLIDAY TABLE I added another version:

mtarler_0-1692889854999.png

this holiday table calcs the holiday for each year you enter under YEAR and 'included' the holiday if indicated in row 1:

mtarler_1-1692889925216.png

 

1 best response

Accepted Solutions
best response confirmed by dlopez20 (Copper Contributor)