Maintaining Formula when Deleting Reference Cells via VBA Macro

I am trying to populate four rows with data from my "records" sheet, but am having trouble mainting the formulas when I run a macro that deletes old record files and replaces with a new one. I got the following formula to work, but then it returns #na after the macro is run because the original reference cells were deleted. My other issue is that the formula I am using, won't really work for when there may only be 3 records since it isn't rematching the lookup value (i.e. it is taking whatever the 3rd row is below the first matched cell).

Formula:

Please Login or Register  to view this content.

I've tried to lock in the refernce by putting the absolute value signs on it, but to no avail...

Any help would be greatly appreciated!

Posted prematurely. Here are the cells that I'd like to maintain their formulas even when the reference cells get deleted.

A7

Formula:

Please Login or Register  to view this content.

A8

Formula:

Please Login or Register  to view this content.

A9

Formula:

Please Login or Register  to view this content.

A10

Formula:

Please Login or Register  to view this content.

After I run the macro, all the Records!$A$1 change to Records!$B$1 and when I try to type in the original formula, I can't get it to return the right values anymore...