I have googled and found some answers to this question that include using the offset and indirect functions, but I can't seem to quite get them to work for what I'm doing. So, I thought I'd post my question here.
I often include a column in my spreadsheet or table that calculates a number so that I can then use conditional formatting to color band my rows of data. (I spend a lot of time looking at duplicate SSNs or people names, etc. So this just helps me visually see what goes together). I've attached a sample file of what I am doing. On the deleted rows tab I deleted row 20 - one of the Giraffes. Now I have the #REF! error for all the rows following. I have just been recopying the original formula to the end of the table, but there must be a better way. Anyone have suggestions? Your help is greatly appreciated.
Hi Pam
In cell B2 enter
=IF(ROW()=2,1,IF(A2=OFFSET(A2,-1,),OFFSET(A2,-1,1),OFFSET(A2,-1,1)+1))
Hope this helps.
Sunny
Hi Sunny,
That worked! Thank you for responding. This also helped me understand the offset function a little better.
Hope you have a great day!
