Relative copy conditional formatting that references other worksheet

Question

I have a group of worksheets, one of which has 'default' values while the others should only have values if they differs from the default worksheet.

To facilitate this, I want to use conditional formatting to change the color of a cell if its value matches the value of the cell in the same position on the default sheet.

I have this formula in the Conditional Formatting 'custom formula is' field:

=match(C4,indirect("Default!C4"),0)

This works well for cell C4, but I want to apply this same conditional formatting to all the cells in a range, and I can't find any way to accomplish this short of manually changing the referenced cell for each and every cell in the document.

Any help?

Answer 1

This does the trick:

=IF(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)=INDIRECT("Default!R"&ROW()&"C"&COLUMN(),FALSE),TRUE,FALSE)

It's not exactly concise, but it works.

Answer 2

Promoting pnuts' comment to an answer: the custom formula

=A1<>indirect("Another Sheet!R"&row()&"C"&column(), 0)

will apply formatting to all cells in a range (such as A1:Z, etc) that you provide in the conditional formatting dialog.

Explanation: "0" indicates notation in R1C1 format (available only in new Google Sheets), the concatenated string computes such notation for a cell to which we compare. This comparison command is pretty slow, so you may want to restrict the range to a part of worksheet that's of current interest.