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

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 438 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 451 Show & Tell
 30 Member Spotlight
 1 SmartStories
 283 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!