Delete without losing references?
 

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

Delete without losing references?
 

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" wrote in message
...
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

Delete without losing references?
 

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

Ed


"Bob Kilmer" wrote in message
...
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" wrote in message
...
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