How do I "cut" only the data from a cell
 

Using Excel 2000. I have 2 sheets in my work book. Cell A1 in Sheet
2 has a simple formula: "=Sheet1!A1". Thus the data in sheet 1 A1 is
always replicated in Sheet 2 A1. However, if I "cut" Sheet 1 A1 and
"paste" somewhere else in the book, Sheet 2 A1 still reads the
original data in Sheet 1 A1 and the formula in Sheet 2 A1 has changed
to refer to the new location of the pasted data.

Normally, this is not a problem but in this case I want the data in
Sheet 2 A1 to read whatever is in Sheet 1 A1 at all times. I do not
want the formula to change in any circumstance.

How can I achieve this without recording a macro or using more than 2
clicks whenever I cut Sheet 1 A1?

Regards
Kev Nurse

In cell A1 on Sheet 2, change the formula to: =INDIRECT("Sheet1!$A$1")
This will continue to return the value in that cell, even if the
original value is moved.

Kev Nurse wrote:
Using Excel 2000. I have 2 sheets in my work book. Cell A1 in Sheet
2 has a simple formula: "=Sheet1!A1". Thus the data in sheet 1 A1 is
always replicated in Sheet 2 A1. However, if I "cut" Sheet 1 A1 and
"paste" somewhere else in the book, Sheet 2 A1 still reads the
original data in Sheet 1 A1 and the formula in Sheet 2 A1 has changed
to refer to the new location of the pasted data.

Normally, this is not a problem but in this case I want the data in
Sheet 2 A1 to read whatever is in Sheet 1 A1 at all times. I do not
want the formula to change in any circumstance.

How can I achieve this without recording a macro or using more than 2
clicks whenever I cut Sheet 1 A1?

Regards
Kev Nurse


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html