CODEWITHSUNDEEP
google-sheetsspreadsheet
fraxture
fraxture

Reputation: 5510

Conditional formatting not working with 'countif' function in Google Sheets

I'm trying to apply a simple custom formula in a conditional formatting rule in Google Sheets, but it's not working for me and I can't understand why.

I have two sheets. The first sheet contains a series of rows, that start with an id:

ID
50013   50  13  Member of the City Council  3   4
50082   50  82  Member of the City Council  3   62
50083   50  83  Member of the City Council  3   130

Then my second sheet contains another similar list of rows, which is a subset of those in the first sheet. What I'm trying to do is highlight the rows in the first sheet where the "ID" field (indicated above) is in the subset in the second sheet.

The rule I'm tryin to use is:

countif('Bernie Intersect 1'!$A$2:$A$22, A2)

But when I put it into the conditional formatting conditional field, I don't get the desired result. I can't see the output, but evidently the field is not coming back with TRUE...

I thought that I might need an = in front of the expression, but if I add that I get an error that the formula is invalid.

Does anyone know what I might be doing wrong here?

Upvotes: 2

Views: 6673

Answers (1)

user6655984
user6655984

Reputation:

Conditional formatting formulas do not allow explicit references to other sheets. Use indirect to get around this restriction: 

=countif(indirect("'Bernie Intersect 1'!A2:A22"), $A2)

With indirect there is no need to say that A2:A22 are absolute references, since they are in the string format anyway; within indirect everything is absolute.

Upvotes: 12

Related Questions