Google Sheets Conditional formatting based on another sheet

1
votes

I built a Google Spreadsheets tracking the price of certain items in each month. The different items are displayed in row 3:30. Different retailers are displays in Column C:M. Each month is displayed in a different sheet. I want to match (for example) Cell C3 to Cell C3 in the month before and color Red if the price has increased, Green if the price has decreased and remain white if the price is the same. I want to do so for each of the cells C3:M30.

I have managed to find a formula to match cell C3 to C3 then cell C4 to C4 etc. untill cell C30. This is the formula I used:

=C3>INDIRECT("OKTOBER 2020!C"&ROW())

This works fine for Column C, but I can't find a way to incorporate Columns D:M in this formula. Is there a way to incorporate the conditional formatting rule for Columns D:M in the same formula? Or should I just add this formula to each row with the corresponding row Letter?

Thanks.

1
google-sheetsconditional-formatting

1 Answers

2
votes

Answer:

You can do this with an ARRAYFORMULA.

Formula:

=ARRAYFORMULA(C3:M30>INDIRECT("OKTOBER 2020!"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&ROW()))

Rundown of this formula:

  • Creates an ADDRESS of a cell which has a row of 1, the current column index, using a relative reference.
  • Substitute the hard-coded row number 1 to extract out the column letter
  • Construct an indirect reference to the current cell using the extracted column letter, the current ROW(), and appending it to the string OKTOBER 2020!
  • Check if this cell is greater than the current cell
  • Run this whole formula on the range C3:M30. This can be expanded to cover additional cells, if necessary.

This formula checks if the price has gone up, for which the conditional formatting should reflect as such. You can also do this for when the price has decreased or stayed the same by changing the initial comparison operator:

Price decrease:

=ARRAYFORMULA(C3:M30<INDIRECT("OKTOBER 2020!"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&ROW()))

No price change:

=ARRAYFORMULA(C3:M30=INDIRECT("OKTOBER 2020!"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&ROW()))

I hope this is helpful to you!

References: