Conditional Formatting across a range of cells with a formula in Google Sheets

Question

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".

I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".

I want to conditionally format the entire calendar so that if any cell in the calendar has a date that matches one in the data set it will format.

I'm using this formula so far, but no dice. Not sure what I'm doing wrong.

COUNTIF(indirect("Training Classes!$E:$E"),A1)>1

The calendar range is B6:X37 just in case that matters.

For the record, the formula does work if I assign it to a specific cell and put it in a cell.

So if I put somewhere =COUNTIF(indirect("Training Classes!$E:$E"),S27)>1) then it is true. Its just the conditional that's having issues.

Answer 1

First of all, sharing your sheet or at least a copy/example is going to get you a much better answer more quickly.

I attempted to recreate your spreadsheet as best I could given your description (completely guessing on the calendar sheet).

Based on my example, all the cells in Calendar!B6:X37 contain a unique date. Each cell takes its date, and searches for it in 'Training Classes'!E:E. If it finds a match, then that cell gets highlighted yellow.

The formula I use for the conditional formatting is:

=ARRAYFORMULA(COUNTIF(INDIRECT("Training Classes!E:E"),B6:X37))

The formula you were using:

COUNTIF(indirect("Training Classes!$E:$E"),A1)>1

I believe this wasn't working because: You said the calendar was in B6:X37, yet you reference A1 (your working formula example uses S27). Also, you are missing an = at the start of the formula, and the >1 appears to be unnecessary.