google-sheetsconditional-formatting

Google Sheets Conditional Formatting of a column based on formula


I cannot figure out the correct syntax to get the conditional formatting I need in google sheets. In sheet1 I have column B with header name Count and then a sequence of 1 to 100 in the rows. In sheet2 I some other counts to create a total. The condition I need to check against is COUNTIF('sheet2'!$B:$B,"mytotal"). (I've named this cell CurrentTotal). I want to turn the cell in sheet1 red based on what my total is.

I have tried various custom formula conditional rules but I haven't managed to find anything that works either by referencing the COUNTIF formula or the named cell (with or without using INDIRECT). What should be the correct setup I should be using to get this to work?

Sheet1.

| Count | | 1 | | 2 | | 3 | | 4 | | 5 |

Sheet2. This is a column of info. The important bit is the count of instances of mytotal.

| Total | | mytotal | | mytotal | | x | | y | | mytotal |

So the countif will be 3 so sheet1 should have the 3 highlighted. If sheet2 gets updated to have an extra mytotal then the highlight on sheet1 should move to 4.


Solution

  • You're very close. Remember that custom formulas for the conditional formatting criteria are evaluated for each cell that the rule is being applied to and the formula needs to return TRUE/FALSE.

    Also, in order to reference a range on another sheet, you need to use INDIRECT.

    In your case, you are applying the rule to Sheet1!B2:B, so writing the custom formula from B2's perspective gives:

    =B2=COUNTIF(INDIRECT("Sheet2!B:B"),"mytotal")