Conditional formatting using a custom formula that references the cell itself

Question

I have a google spreadsheet in which there are columns for each day (with a date in the top cell), and in each cell there is a row indicating the status.

I need to apply conditional formatting to cells based on the date criteria of a column in the past and the text inside the cell, starting with the “OK” substring.

It seems like I should use a custom formula to deal with the date part, but how can I refer to the contents of the cell itself? I tried to reference the cell using CELL("contents",ADDRESS(ROW(),COLUMN()) , but this returns Error: Argument must be a range .

Answer 1

With conditional formatting (both in Excel and in google docs) you just use a formula that applies to the top left cell of the range .... and you can just refer to the cell by the cell in it, so if the dates are in B1:J1 , and your status data is in B2:J100 , then select this last range and (in conditional formatting) apply the formula that applies to B2 , i.e.

=AND(B$1<TODAY(),LEFT(B2,2)="OK")

This will work for the entire range.

You should use absolute / relative links, as if you were copying the formula down / over the range, so you need $ in B$1 , because you want each line to refer to line 1 .... although you want to go through it so that it changes to C$1 , D$1 , etc., therefore, there is no $ before the column letter