I have a list of characters down one column on Sheet 1, Col. C; on Sheet 2,
Col. AA, those characters are part of a longer string - or should be. I
need to identify and delete the longer strings that do not contain the
matching character strings. I have two formulas in adjacent columns:



=(LEFT(RIGHT(AA2,14),10))

returns the characters from the longer string that should match the column
on the first sheet and shows them on Sheet 2, Col. AL.



=IF(Sheet1!C2=AL2,"","OFF")

compares the list returned from the first formula above to the list of
characters on the first sheet. If they do not match, indicating the long
string does not contain the short string, it shows "OFF" in Sheet 2, Col.
AM.



All this works - until I delete the longer string! Then the formula in AL
loses its reference; if I delete that, then AM loses *its* reference! Right
now, I have to delete the offending string and reinsert the formulas after
every deletion.



How can I get around this?



Ed