Question regarding inserting rows and data.
 

In Sheet1 I have the following (from A1 to C3):

2 3 4
3 4 7
8 8 1

In Sheet2 I have the following (from A1 to C3):

=Sheet1!A1+0.001 =Sheet1!B1+0.001 =Sheet1!C1+0.001
=Sheet1!A2+0.001 =Sheet1!B2+0.001 =Sheet1!C2+0.001
=Sheet1!A3+0.001 =Sheet1!B3+0.001 =Sheet1!C3+0.001

....which means that the values displayed in Sheet2 are the following:

2.001 3.001 4.001
3.001 4.001 7.001
8.001 8.001 1.001

Now, suppose I insert a new row enter new data in Sheet1, like so:

2 3 4
3 4 7
1 1 1
8 8 1

Sheet2 looks the same, and the bottom row's formulae are changed to:
=Sheet1!A4+0.001.....=Sheet1!B4+0.001.....=Sheet1! C4+0.001

So, my question is: Is there any way to automatically update Sheet2 so that
it looks like this?

2.001 3.001 4.001
3.001 4.001 7.001
1.001 1.001 1.001
8.001 8.001 1.001

True, I could simply go into Sheet2 and manually insert another blank row
and fill down, but I don't want to have to do that. I only want to work with
Sheet1, I don't want to have to do anything to Sheet2, but I want Sheet2 to
be changed all the same. Understand?

In order to NOT have references move when you insert cells, you need to have
them be text. That's where the INDIRECT function comes in. In sheet 2,
A1:C3:
=INDIRECT("Sheet1!A1")+.001 =INDIRECT("Sheet1!B1")+.001
=INDIRECT("Sheet1!C1")+.001
=INDIRECT("Sheet1!A2")+.001 =INDIRECT("Sheet1!B2")+.001
=INDIRECT("Sheet1!C2")+.001
=INDIRECT("Sheet1!A3")+.001 =INDIRECT("Sheet1!B3")+.001
=INDIRECT("Sheet1!C3")+.001
and more if you wish.
Now, inserting on sheet1 won't have any effect on sheet2, and these formulas
will still refer to A1:C3 on Sheet1.
HTH

Bob Umlas
Excel MVP

"Mr. Snrub" wrote in message
...
In Sheet1 I have the following (from A1 to C3):

2 3 4
3 4 7
8 8 1

In Sheet2 I have the following (from A1 to C3):

=Sheet1!A1+0.001 =Sheet1!B1+0.001 =Sheet1!C1+0.001
=Sheet1!A2+0.001 =Sheet1!B2+0.001 =Sheet1!C2+0.001
=Sheet1!A3+0.001 =Sheet1!B3+0.001 =Sheet1!C3+0.001

...which means that the values displayed in Sheet2 are the following:

2.001 3.001 4.001
3.001 4.001 7.001
8.001 8.001 1.001

Now, suppose I insert a new row enter new data in Sheet1, like so:

2 3 4
3 4 7
1 1 1
8 8 1

Sheet2 looks the same, and the bottom row's formulae are changed to:
=Sheet1!A4+0.001.....=Sheet1!B4+0.001.....=Sheet1! C4+0.001

So, my question is: Is there any way to automatically update Sheet2 so
that
it looks like this?

2.001 3.001 4.001
3.001 4.001 7.001
1.001 1.001 1.001
8.001 8.001 1.001

True, I could simply go into Sheet2 and manually insert another blank row
and fill down, but I don't want to have to do that. I only want to work
with
Sheet1, I don't want to have to do anything to Sheet2, but I want Sheet2
to
be changed all the same. Understand?