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.
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")