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

H

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

T

Teethless mama

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

T

Teethless mama

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

S

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

S

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

H

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.

H

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.

H

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.

H

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.

H

HPH

Everything was fine, until i inserted a column...
Your clever trick unfortunately can't cope with column inserts.

So I am open to new suggestions.

H

HPH

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

Function RelativeCellValue(Optional column, Optional row)
Application.Volatile
End Function

It provides a complete relative referencing.