1

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?

Improve this question

1 Answer 1

Reset to default
2

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()))

References:

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is great, works like a charm! I do have one more question. Right now, if Cell C3 contains a value in for example the month October. Cell C3 in November could be empty but still turn green. Since when it's empty, it's always lower than the one in the other sheet. I've tried adding a rule for Cell is empty > White formatting, but that trick does not work. Any way to incorporate this in the formula? You have already been of great help, much appreciated!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.