insert row, keep formula

Hi,

I am using excel for bookkeeping. Sometimes when I have added several rows of information, I discover that I missed some info and I need to insert a row a few rows above (everything goes by date, and it has to be in the right order). When I do this, all my formulas shift down of course, but this is not convenient.

Example- I have the following 2 rows of data in 3 columns:
A1 | B1 | =A1*B1
A2 | B2 | =A2*B2

When I insert a row in between I get:

A1 | B1 | =A1*B1

A2 | B2 | =A2*B2

But I want:

A1 | B1 | =A1*B1

A3 | B3 | =A3*B3

so I can insert the row of '2' values, like such:

A1 | B1 | =A1*B1
A2 | B2 | =A2*B2
A3 | B3 | =A3*B3

Part 2 of the problem: the first sheet is connected to a second sheet with the formula =sheet1!A1, etc. Essentially Sheet 2 is just a copy of certain values of sheet 1 (the input sheet) which it then uses to calculate some statistics. I do no manual input on sheet 2.

When I insert a row on sheet 1, it does not insert a row on sheet 2, but it does change all of the formulas by +1, so now =sheet1!A2 changes to =sheet1!A3.

Is there any way to lock the references on sheet 2 so that for example the value in sheet2A2 ALWAYS equals the value in sheet1A2, no matter what happens on sheet 1?
I tried =sheet1!$A$2, but that also becomes =sheet1!$A$3 when I insert a row on sheet 1.


So far I've just been correcting it manually every time by retyping the formulas and re-entering all the data, and then dragging down the series. Besides being tedious, this also significantly increases the margin of error. There's got to be a better way!!!

Any ideas?

~Charles

How are you inserting rows?

1/. Inserting between A1 & A2 as in your example will give you

A1 | B1 | =A1*B1

A3 | B3 | =A3*B3

Then fill down C1:C2 to insert your formula

What is the formula you are using that is giving you trouble?

2/. On Sheet2 Select the used section (plus the number of rows you have added) of ColumnA and fill down after you have added your changes.