Reference sheet (sheet1) keeps changing when adding a new row on data sheet (sheet2)

Hello,

Question:

I have a summary sheet (Executive Summary (ES)) that tallies up the data from different sheets within the same workbook. On each of the sheets after ES I have a VBA button that adds a new row for the addition of new data. The new row is entered on row "10".

Put curser in "A10" click button
Data is moved to "A11"
Row "10" is now blank to enter new data

This works great.

On the ES sheet I have a formula that does the following:

Please Login or Register  to view this content.

This works great also, until I add a new row in, then the the following happens:

Please Login or Register  to view this content.

The code shifts down to A11 and A102.

How do I stop the code from shift down?

I have tried the following:

$A$10
$A10
A$10

Nothing works.

Thanks