Conditional formatting which changes the reference cell when it's copied and pasted

Alright, got it. I had to use the indirect() and address() formulas to give me a relative reference. I think I'm misremembering something.

=if(A2=indirect(address(row()-1, column())), TRUE, FALSE)

  1. Basically: If the cell in the second row is equal to the cell above it, it'll return true.
  2. Address(row, column) gives you a cell value; so address(1, 2) would return B1. In this case, the row() and column() functions return the number of whatever row and column the cell the formatting is applied to is in. Then row() - 1 means that instead of referencing the row value of that cell, it's referencing the one directly above it
  3. Indirect turns that that text output into a cell reference; so Indirect(B2) would just make it so that the formula treated B2 as a cell reference instead of a text string
  4. Then the if() formula just returns TRUE or FALSE depending on whether the cell being formatted is equal to the value above it or not. If you're looking for less than, or greater than, or whatever you can just change the equals sign
/r/googlesheets Thread Parent