Conditional Formatting from another sheet

46
votes

I'm trying to have a cell on Sheet A check if it's either > or < the value in a cell on Sheet B, then change its color accordingly. Under the custom formula I use: =A1>("SheetB!A1"), but it doesn't seem to work. I use the color Green for the > and the color Red for the <. Every time the rules are saved it will always display A1 on Sheet A in red.

Is the function wrong? Or is it not possible to have a Conditional Format even search across sheets?

4
google-sheetsgs-conditional-formatting
It accepts the rule and applies the < format to the cell. – Josh
You are comparing A1 with the text string "SheetB!A1", as you have the formula written. But writing it as a direct cell reference rather than a string won't work either (it might be worth editing this fact into the question... what do you think?), but there is a workaround, as described in my answer. – AdamL

4 Answers

87
votes

For some reason (I confess I don't really know why) a custom formula in conditional formatting does not directly support cross-sheet references.

But cross-sheet references are supported INDIRECT-ly:

=A1>INDIRECT("SheetB!A1")

or if you want to compare A1:B10 on SheetA with A1:B10 on SheetB, then use:

=A1>INDIRECT("SheetB!A1:B10")

=A1>INDIRECT("SheetB!"&CELL("address",A1))

applied to range A1:B10.

5
votes

You can do this by referencing the cell and row number in the current sheet, so as you drag-copy that conditional formatting to other rows it will reference the correct cells. In the below equation I am coloring cells based on the exact same cell in some other sheet named "otherSheetName" in this example. If for example you want to color cell B2 in Sheet2 if the cell B2 in otherSheetName contains the text "I Like Dogs" you would go to cell Sheet2!B2 , click condition formatting, choose equation from the drop down and paste the below equation. 

=IF(INDIRECT("otherSheetName!"&ADDRESS(ROW();COLUMN()))="I Like Dogs";1;0)
1
votes

Comparing strings instead of numbers for a conditional formatting rule, you can use:

=EXACT(A1,(INDIRECT("Sheet2!A1")))

Case sensitive.

0
votes

There is one trick/bug: if you have conditional formatting in Sheet1 that explicitly references itself (e.g., the formula is Sheet1!$C$2), you can copy the conditional formatting to Sheet2 with Paste special > conditional formatting and it will "work"... as long as you don't touch anything:

  • if you try to edit the conditional formatting in Sheet2, then you'll get an "Invalid formula" error.
  • if columns/rows change in Sheet1 such that they affect the conditional formatting (e.g., row/column inserts), this is not reflected in Sheet2 (keep in mind that the indirect trick mentioned by @AdamL will also not reflect column/row updates either, so it's a wash in this respect).