Cross sheet references (=Sheet2!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


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=513409

Cross sheet references (=Sheet2!B4)
 

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

Cross sheet references (=Sheet2!B4)
 

-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??


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=513409

Cross sheet references (=Sheet2!B4)
 

=indirect("sheet2!b4")

Will always point at B4.

DejaVu wrote:

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

--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=513409

--

Dave Peterson