Delete without losing references?

[Ed]

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

 

[Bob Kilmer]

How about copying the strings that satisfy the criteria into a third
worksheet? Use something like this on the third sheet.

=IF(Sheet1!C2=AL2,Sheet2!AA2,"")

Then replace the sheet you would have deleted the strings from with the
third.

Bob Kilmer

 

[Ed]

Thanks, Bob. I didn't think of doing things that way, but it makes a lot
more sense.

Ed