Saturday, September 27, 2014

yogi_Conditionally Format Each Cell Of Sheet2 That Is Different From That Of Sheet1

                    Google Spreadsheet   Post  #1775
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Sep-27-2014
post by  Bryan Blair:
New Conditional Formatting option - Custom formula is
How would you use =indirect("Sheet1!A1") as a custom formula for conditional formatting across a range of cells.

Here is my current setup:

2 sheets - Sheet1 and Sheet2

I want to compare ALL of the cells in Sheet 2 to Sheet 1.  If the contents of each cell are different, I want to change the background color of the cell in Sheet 2.  This is easy enough if the 2 cells are on the same sheet. But, the indirect() function appears to add something that I don't completely understand.

I have tried the custom formula (above) in the cells for Sheet 2, and I can get it to work for single cells - but, as soon as I try to apply the custom formula to a range of cells, it doesn't work - the reference cell in the indirect formula is not a relative reference - it doesn't change to A2, A3... etc - it remains A1 for the entire range.  Why?