How do I fix a formula in a cell?
 

I'm 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 "=Sheet1!A1" in Sheet 2 A1 to change in any circumstance.

How can I achieve this without recording a macro or using: copy, paste,
return to original cell and delete it (too many actions) ?

Regards
Kev Nurse

=INDIRECT("Sheet1!A1")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kev Nurse" wrote in message
...
I'm 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 "=Sheet1!A1" in Sheet 2 A1 to change in any circumstance.

How can I achieve this without recording a macro or using: copy, paste,
return to original cell and delete it (too many actions) ?

Regards
Kev Nurse

=INDIRECT("Sheet1!A1")

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Kev Nurse" wrote in message
...
I'm 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 "=Sheet1!A1" in Sheet 2 A1 to change in any circumstance.

How can I achieve this without recording a macro or using: copy, paste,
return to original cell and delete it (too many actions) ?

Regards
Kev Nurse

Nick, thank you very much. I would never have found that function. You
have saved the day for me.

Regards
Kev Nurse

Bob, thank you very much. I would never have found that function. You have
saved the day for me.

Regards
Kev Nurse