1

I have a Google spreadsheet which contains two worksheets. Each sheet contains a column of URLs followed by 6 columns of data. I would like to highlight on the second sheet if the value is different, ideally styling the particular cell with a red or green background depending on the value.

Sheet 1

╔═════════════╦═════╦════╦═════╦════╦════╦════╗
║     URL     ║  d1 ║ d2 ║ d3  ║ d4 ║ d5 ║ d6 ║
╠═════════════╬═════╬════╬═════╬════╬════╬════╣
║ example.com ║ 400 ║ 11 ║ 2.2 ║ 50 ║ 60 ║ 70 ║
╚═════════════╩═════╩════╩═════╩════╩════╩════╝

Sheet 2

╔═════════════╦═════╦════╦═════╦════╦════╦════╗
║     URL     ║  d1 ║ d2 ║ d3  ║ d4 ║ d5 ║ d6 ║
╠═════════════╬═════╬════╬═════╬════╬════╬════╣
║ example.com ║ 400 ║ 11 ║ 2.2 ║ 55 ║ 68 ║ 90 ║
╚═════════════╩═════╩════╩═════╩════╩════╩════╝

In this case, I'd like the 55, 68 and 90 value to be highlighted as they are different from sheet 1.

My quick solution was to export each sheet as a CSV and then run a diff over them to highlight changes, but this doesn't fully achieve what I'd like.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Alexander Holsgrove
  • 1,795
  • 3
  • 25
  • 54
  • @Rubén I suppose Alex has tried conditional formatting, but the issue is that the data to compare to is on another sheet. –  Mar 14 '16 at 23:55
  • @404 I agree that the issue is that the built-in conditional formatting feature doesn't allow to use references to another sheet in custom formulas, but this should be mentioned in the question, I think. – Rubén Mar 15 '16 at 01:53
  • Related: http://stackoverflow.com/questions/28905351/google-docs-spreadsheets-conditional-formatting-referencing-other-sheets – Rubén Mar 15 '16 at 01:58
  • Apologies for not adding "what have I tried". My quick solution was to export each sheet as a CSV and then run a diff over them to highlight changes, but this doesn't fully achieve what I'd like. – Alexander Holsgrove Mar 15 '16 at 09:10
  • @AlexHolsgrove it's better to include that in the question as comments aren't intended to be permanent. – Rubén Mar 15 '16 at 12:58

2 Answers2

2

Conditional formatting

The conditional formatting can do that, but it can only reference cells within the same sheet. To get around this restriction, place the command such as 

=arrayformula(Sheet1!B2:H2)

somewhere on the second sheet (e.g., in cell AB2). Then use conditional formatting for the range B2:H2 with the rule "value is not equal to ... =AB2".

(The conditional formatting formula is entered as it should be read for the upper left corner of the range being formatted).

Apps Script

Another approach, avoiding duplication of data, is to use an Apps script. Here is sample code that sets backgrounds according to the situation you described. Note this will not update automatically unless it's set to trigger on every edit.

The aforementioned approach with conditional formatting is to be preferred.

function compare() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');
  var values1 = sheet1.getRange('B2:H2').getValues()[0];
  var values2 = sheet2.getRange('B2:H2').getValues()[0];
  var backgrounds = []; 
  for (var k = 0; k < values1.length; k++) {
    backgrounds.push(values1[k] == values2[k] ? 'white' : 'red');
  }
  sheet2.getRange('B2:H2').setBackgrounds([backgrounds]);
}
  • The script was very simple to write thanks to your example. I had to add a bit more logic to show two colour states for each value (if it had increased or decreased). I added a menu item so that I could re-run the comparison as and when required. Thanks for your answer. – Alexander Holsgrove Mar 15 '16 at 11:47
2

Overview

Use INDIRECT in the custom formula of the conditional formatting rule like the following one:

=INDIRECT("Sheet1!R"&ROW()&"C"&COLUMN(),false)&lt>B2

Explanation

The Google Sheets built-in conditional formatting feature doesn't allow the use of references to different sheets directly, but it allows the use of INDIRECT.

The above formula includes two "hacks". The first was mentioned in the overview, the use of INDIRECT. The second "hacks" is to use the R1C1 notation together with ROW() and COLUMN() to make the conditional formatting rule very flexible compared with the use of a hardcoded reference and to behave like a "normal" reference.

Conditional formatting rule

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • This seems to work for a single row, but I'm trying to apply it to over 100 rows (B2:G100) and then using the formula `=INDIRECT("Sheet1!R"&ROW()&"C"&COLUMN(B2:G100),false)<>B2:G100` but this doesn't seem to work. Do I need to apply this one row at a time? – Alexander Holsgrove Mar 15 '16 at 11:12
  • I simplified the formula. There is no need to apply the conditional rule one row at a time, just set the value in the "apply to range" as it's shown in the recently added image. – Rubén Mar 15 '16 at 12:56
  • Thanks. I'll give that a try too. Although it's applied to the range B2:G100, should the formula not reference the range as well, instead of just <>B2 ? – Alexander Holsgrove Mar 16 '16 at 12:43