1

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.

bumped to the homepage by Community 2 days ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • So what’s the question? – Alex Sep 20 '18 at 19:05
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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