Deleting rows in excel and recalculating formulas

[aoe]

I have the following cell formulas

A1: 100
A2: =A1+1
A3: =A2+1
A4: =A3+1
A5: =A4+1

I would like to delete a row, eg. A3 and not have the following rows (A4, A5) get #REF! errors. Instead, I want them to recalculate so that A4 becomes the new A3 and takes on the formula =A2+1

Is there a way to code this?

Thanks.

 

[trunten]

A1: 100
A2: =OFFSET(A2,-1,0)+1
A3: =OFFSET(A3,-1,0)+1
etc...

 

[Aladin Akyurek]

I have the following cell formulas

A1: 100
A2: =A1+1
A3: =A2+1
A4: =A3+1
A5: =A4+1

I would like to delete a row, eg. A3 and not have the following rows (A4, A5) get #REF! errors. Instead, I want them to recalculate so that A4 becomes the new A3 and takes on the formula =A2+1

Is there a way to code this?

Thanks.

Non-volatile...

=SUM(INDEX(A:A,1),ROWS($A$2:A2))