Keep absolute reference even when inserting rows in Excel 2007

Building on @ktdrv's answer (I can't comment): =indirect("F"&ROW()) would be $F2 if it's a reference that needs to be dragged down multiple rows. A minor drawback with =indirect() is that you lose cell reference highlighting for the formula.

Try =indirect("F2"). This will work if you know that the top-right cell of the table is always going to be $F$2.

You could also use the Offset Function:

http://office.microsoft.com/en-us/excel-help/offset-function-HP010342739.aspx

or

https://support.office.com/en-us/article/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66