Referring to Adjacent Row

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.

Comments

  • David Hersher
    edited 02/11/19

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!