Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table for survey data w/ questions as Rows & poss answrs as | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
inserting rows through external data source | Excel Discussion (Misc queries) | |||
Searching for data and inserting new rows | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |