Conditional Formatting from another sheet

Question

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?

Outstand

It accepts the rule and applies the < format to the cell. – Outstand

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. – Embarrass

Answer 1

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.

Embarrass

Remember to add single-quotes if the sheet name has spaces, e.g. =A1>INDIRECT("'Sheet B'!A1") – Cologne

Also happy to see this works with named ranges: INDIRECT("your_named_range") – Lophobranch

WATCH OUT! I did this in a conditional formatting rule applied to over 1000 cells, and it completely tanked my spreadsheet's performance in a way that made it almost unusable but was hard to diagnose. Having a ton of INDIRECTs around is dangerous, I think because Sheets doesn't know what they depend on so it has to constantly recalculate them. – Body

It doesn't seem to work anymore, warning dialog comes up as soon as I type in INDIRECT( – Muddle

You are really genius man. I got my solution to highlight the different cell values only using below formula in conditional formatting -------------------------- =A1<>INDIRECT("Sheet1!"&CELL("address",A1)) – Misconstrue

Answer 2

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)

Orthoptic

Answer 3

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

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

Case sensitive.

Trudi

That works for a single cell, but as the "Sheet2!A1" is protected by double quotes, it won't respond to copy-paste through multiple cells and won't change the value to A2, A3, etc. – Aria

Answer 4

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

Toon

Answer 5

I was able to compare two sheet and highlight the differences on the second sheet using conditional formatting :

=A1<>(INDIRECT("Sheet1!"&Address(Row(),Column(),)))

Casimir

Answer 6

Using other sheets in conditional formatting is not supported. As a workaround:

• Clone the data from SheetB in SheetA using =SheetB!A1, use e.g. columns Y+
• [Hide columns Y+]
• Use =A1>Y1 for conditional formatting.

Ramage