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.

  • So what’s the question?
    – Alex
    Sep 20, 2018 at 19:05

1 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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.