Insert a new line, without destroying the consistency of the formu

[HPH]

How do I insert a new line in a excel spreadsheet without destroying the
consistency of the formulas?

Cut down to a minimum, the following is what I want.

Starting point:
B2=B1+A2
B3=B2+A3

Desired result after inserting a new line BETWEEN line 2 and line 3:
B2=B1+A2
B3=B2+A3
B4=B3+A4

I have been using SuperCalc for many years now, where this is a very simple
command. But I have newer been able to make the shift to excel. Just because
I could not figure out how to make this simple insert.
I have been trying at least four different types of relative referencing,
but they are all producing the wrong result:
B2=B1+A2
B3=<empty>
B4=B2+A4

 

[Teethless mama]

=INDIRECT("B"&ROW()-1)+INDIRECT("A"&ROW())

 

[Susan]

i think what the original poster is looking for is that the formula
will automatically populate the newly-inserted line, in the same
column as the previous row (& the row after)............. if i
understand him/her properly.
the only way i can see to do it is to insert the new line, go to the
first cell above that contains the formula, and pull it down
throughout the rest of the list, thereby inserting the formula in the
blank cell & also correcting the rest of the list.

susan

 

[HPH]

Hello Susan
You are absolutely right. But this is just the simplest example.
In my real spreadsheet there are hundreds of columns and rows, and many
inserts. So it is very tedious and risky to do this mouse maneuver. I would
be very thankful for a real solution.

 

[HPH]

Hello Teethless

I have just tested your solution. And it actually has the right behavior
when inserting a new line. Thank you very much.

Now I just have to figure out how to insert a line with the right formulas
with one key combination. But this must be a simple operation described
somewhere in the documentation.

 

[HPH]

well actually it won't be a problem in my current calculations. It was just
an observation during testing.

I had another advice to create the following Visual Basic code:

Function RelativeCellValue(Optional column, Optional row)
Application.Volatile
answer = Application.Caller.Offset(row, column)
RelativeCellValue = answer
End Function

It provides a complete relative referencing.