Referring to Adjacent Row

Art Schneiderheinze

I am using the following formula in Color column to shade every other row in a sheet:

ROW 1: No formula

ROW 2 = IF(Color1 = 0, 1, 0)

ROW 3 = IF(Color2 = 0, 1, 0)

I have over 1,000 rows. This works fine. However, if I insert a new row, I have to drag the formula from the row above all the way down to the last row the fix the shading.

Is there a generic way to refer to "the row above"? So, instead of saying IF(Color1 = 0, 1, 0) and adjusting the cell reference for every row, saying something like IF(The value of the cell in the row above = 0,1,0)?

Then every row would have the same formula, no need to drag the formula down to all of the rows whenever I insert a new row.

David Hersher

This might be more work than it's worth, but:

Top row of column Color= "1"

2nd row =Color1 + 1.  Row reference will update as you copy down.

When you insert a row, you'd only have to copy the formula from the cell above the new, empty cell to the first row below it to continue the calculation.

Then, a 2nd column that's =RIGHT(Color1, 1).  That runs all the way down the column.

Then a conditional formatting rule for odd numbers in the RIGHT column and another for evens.

Someone will probably be along with something easier.

Dave