Google Sheets – Relative Copy Conditional Formatting Referencing Other Worksheet

conditional formattinggoogle sheets

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?

Best Answer

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.

Related Topic