VLOOKUP anchored cell reference changing when macro runs

[Guest]

I have a vlookup formula in a cell that looks up a record ID in another
workbook and autofills certain data. I have the cell range as $A$2:$Z$30000.
The macro that adds new record #s to the other workbook copies a row of
info. and inserts the row above Row 2 of the sheet. When I go back to the
Vlookup formula, $A$2 has switched to $A$3 (so the formula can never find any
new rows / data added).

I tried to record a macro that pastes the update info at the bottom of the
list (A1 the Ctrl Down Arrow) but the macro doesn't remain relative -- always
going to the same cell and overwriting previous entries. ~ not quite
technical enough ... Questor

 

[Guest]

Modify your macro to do the insert the new row at row 3 instead of row 2,
that will keep the new row within the previously defined range and Excel will
adjust the end of the range for you: $A$2:$Z$30000 will become $A$2:$Z$30001

 

[Dave Peterson]

Can you use all the rows?

=vlookup(x99,sheet2!$a:$z,18,false)