Cross sheet references (=Sheet2!B4)

D

DejaVu

This is probably a stupid question, but I can't seem to figure it out.


Say, for example I have the following formula in _Sheet1-Cell_A1_
*=Sheet2!B4*. In sheet2 I have the following Data:
B4: 25
B5: 50
B6: 75
B7: 100

_Sheet1-Cell_A1_ will now show 25. I want to be able to delete row 4
in sheet 2, and have _Sheet1-Cell_A1_ keep the reference to
_Sheet2-Cell_B4_. _Sheet1-Cell_A1_ should now show 50.

This does not work... I keep getting a reference error:
*=Sheet2!#REF!*

Any ideas?
TIA,

DejaVu
 
P

Pete_UK

The reason you get the #REF error is that the reference you used to
have (i.e. to row 4) has been deleted! The way round this is to use the
OFFSET( ) function (details in Excel Help) or to stop people deleting
rows.

Hope this helps.

Pete
 
D

DejaVu

-stop people deleting rows.-

I can't stop people from deleting rows. It's a spreadsheet that get's
updated montly and with each update, it pushes the old data off and
adds the new data.

Is there any way to make a cell "always" point to another constant
cell??
 
D

Dave Peterson

=indirect("sheet2!b4")

Will always point at B4.
This is probably a stupid question, but I can't seem to figure it out.

Say, for example I have the following formula in _Sheet1-Cell_A1_
*=Sheet2!B4*. In sheet2 I have the following Data:
B4: 25
B5: 50
B6: 75
B7: 100

_Sheet1-Cell_A1_ will now show 25. I want to be able to delete row 4
in sheet 2, and have _Sheet1-Cell_A1_ keep the reference to
_Sheet2-Cell_B4_. _Sheet1-Cell_A1_ should now show 50.

This does not work... I keep getting a reference error:
*=Sheet2!#REF!*

Any ideas?
TIA,

DejaVu
Click to expand...
 
Top