conditional formattinggoogle sheetsworksheet-function
I would like to get a cell in a particular row above the current cell.
This is to be used in conditional formatting, so I can't specify a particular cell, e.g. A$4—the reference would be the same for whole range.
What I need is something like:
GET_CELL_AT( COLUMN(CURRENT_CELL), 4 )
What I use it for is to color a cell red if it's bigger than a treshold in the other cell.
Generally: the way to refer to "this" cell is to enter notation for the upper-left corner of the range being formatted. For example, if the range is C1:C, then C1 means "this cell".
For example, formatting C1:C with custom formula
=C1 < today() - 14
will format all cells in C with dates two weeks in the past.
If the range you want to format begins with row 8, and you think you may be inserting rows above that (thus shifting the range), then this formula can be used:
=and(C1 < today() - 14, row(C1) >= 8)
The formatting will apply only to rows starting with 8, but the range being the entire column, the formula will handle insertion of rows above row 8.
UPDATE FROM COMMENTS:
You need 3 custom format settings, all applide to the range B3:B. The First is set to
Format cells if... Cell is empty
and the fill is set to None
The second and third are Custom Formula where the Formula are:
For red:
=B4<B3
and for green:
=B4>B3
OLD INFO FOR Comments
You need 2 conditional format rules. Both applied to cells B2:B, with Custom formula is selected. In the first, set the formula to:
=B1<B2
and set the color to the Red you want to see. In the second, change the formula to
=B1>B2
and select the green color you wish.
Best Answer
Solved with:
ADDRESSgives me a string like "$A$1" andINDIRECTmakes it a cell reference.