Excel not pasting the formula or reference

[Lindsey]

I am trying to take a worksheet "apple" and copy it to a new workbook
"banana".
It works for another worksheet but not this one.
I am highlighting the whole worksheet and copying it and then opening
the workbook sheet and clicking the corner box and selecting paste.

When it works correctly the cell would look like this:
cell from "apple"
='sheet1'!A2

then when it is pasted over to "banana"
='C:\Documents and Settings\Desktop\PERSONAL\[apple.xls]Sheet1'!A2

now when i paste it over it just pastes what the cell displays which
happens to be the answer '2'. When I click on this cell in the new
workbook, it isnt associated to the old worksheet (no function or
reference) which i need.

I need to have this new workbook to update automatically when I add to
the old one but with the way it is pasting, this isnt happening.

What did I do to cause this?

 

[Luke M]

It sounds like you have the workbooks opened in different instances of XL,
rather than a single instance. While this owuld be handy if you need
different calc settings, since you're wanting to setup data relationships,
you need to make them both be opened in same instance.

Close all XL workbooks, then open the "apple" workbook. From XL, open
"banana". You should now be able to copy the sheet over.