If the row above J13 is never deleted, then formula in Sheet 2, B12 could be
"=OFFSET(Sheet1!J12,1,0)". Or reference any cell on Sheet 1 and adjust row &
Col offset numbers.

"unexpected" wrote:

Is there a way to set a formula so that if the row that is referenced is
deleted the formula automatically adjusts to the row that takes its place. I
tried an absolute reference but still get the #REF! error.

Specifically the formula in cell B12 on sheet 2 refers to cell J13 on sheet
1. If I delete row 13 on sheet 1 I would like cell B12 to now refer to the
cell that was formally J14 but has now moved up into J13. It is easy enough
for me to fix with an autofill or a macro but someone else will be deleting
the rows and the #REF! error then destroys the reference for all people
sharing the file until I get back and make the fix. I'm also trying to avoid
deploying macros to other users as much as possible.

Also having the inverse of the problem in that my VBA references DO NOT
adjust for changes to the sheet like formulas adjust. i.e. the references in
a VBA instruction such as:
Sheets("Sheet1").Range("G4").Value = Sheets("Sheet1").Range("E50")
must be reset to E49 if row 45 is deleted, whereas formulas on the sheet
automatically change their references to E49.

Excel just is not smart enough to read my mind I guess. Any help greatly
appreciated.
ed