Odd Excel Behavior

Question

I have a workbook in which column A of 'Sheet 2' writes the contents of column D of 'Sheet 1', i.e. Cell A2 contains formula =Sheet 1!D2, cell A3 =Sheet 1!D3. This is written with the intent that if a new item is added to 'Sheet 1', 'Sheet 2' will move its entries down a row. So for example if I add a row in 'Sheet 1' between A2 and A3 (making a new A3, changing the old A3 to A4, and having the same effect on all cells below), I would like 'Sheet 2' to blindly output whatever is in the cell that it references. Instead, when I add a row to 'Sheet 1', 'Sheet 2' automatically adjusts its formula in the requisite cell. To continue with the example; If I add a row between A2 and A3 in 'Sheet 1', 'Sheet 2' will change its formula from =Sheet 1!D3 to =Sheet 1!D4, and will leave the sheet without a =Sheet 1!D3.

Put another way for clarity:

Before New Row

Sheet 1 Sheet 2

D2 Lions Lions (=Sheet 1!D2)

D3 Ants Ants (=Sheet 1!D3)

D4 Blue-jays Blue-jays (=Sheet 1!D4)

D5 Sharks Sharks (=Sheet 1!D5)

After New Row

Sheet 1 Sheet 2

D2 Lions Lions (=Sheet 1!D2)

D3 Squirrels Ants (=Sheet 1!D4)

D4 Ants Blue-jays (=Sheet 1!D5)

D5 Blue-jays Sharks (=Sheet 1!D6)

D6 Sharks

Is there anyway to stop Excel from automatically changing the formula? If not, is there a way to compensate for this with another formula or widget within the program?

Thanks

Answer 1

In A2 of Sheet 2:

=INDEX('Sheet 1'!$D:$D,ROW())

Replace Sheet 1 with the actual name of that sheet. Then fill down.

Answer 2

Perfect, thank you!