How to avoid #REF! error when rows are deleted|General Excel Questions & Answers|Excel Forum|My Online Training Hub

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.