Formula Error when entire row is deleted
 

Hi,

I have given a vlookup formula in A1 which is refering to A1 in sheet2.
Its working fine except when we delete or insert a row. If we delete
row A1, then the formula in A1 is refering to A2 in sheet2 and not A1.

The formula I have given is
=IF(SEARCH("Test",A1,1),"Test",VLOOKUP(LEFT(A1,19) ,'sheet2'!$A$1:$B$250,2,))


And if I delete the first row, the formula in A1 says:

=IF(SEARCH("Test",A1,1),"Test",VLOOKUP(LEFT(A2,19) ,'sheet2'!$A$1:$B$250,2,))

Any idea how to sort out this?

-Dileep

Formula Error when entire row is deleted
 

Sorry, a small change. The formula is in B1 and not in A1.