Paste link only - not value

[Kurt]

I have worksheet (destination) in which several cells are linked to a
worksheet (source) in another workbook. My destination worksheet has
not been updated with the latest values in the source sheet, so all
the cells are filled with 0s, etc.

Cell D2 in my destination worksheet is currently linked to V2 in the
source worksheet. I'd like to make E2 be linked to V2. When I click on
V2 (source), choose Copy, click on E2 (destination), and choose Paste
Special > Paste Links, two problems occur:

1. The link is copied but it's copied as $V$2 (whereas all of the
other links don't have the $ sign).
2. The current value in V2 is also copied (i.e., it updates the cell
in my destination sheet when I chose Paste Link). I just want to copy
the link.

I've also tried create the link by starting from the destination
workbook (i.e., type an equal sign in E2, switch to the source
workbook, click V2), press Enter.) Same thing happens.

Suggestions?

(By the way, while I make changes, I've done everything I can to
prevent my destination worksheet from being automatically updated:
Under Tools > Options > Calculation tab, I've checked Manual,
unchecked Recalculate before Save, Unchecked Update remote references,
and unchecked Save external link values.)

 

[Jim Cone]

Press the F4 key to add/remove "$" in the active cell.
If a cell contains a formula the result of the formula will be displayed.
If you want to hyperlink one cell to another choose "Hyperlink..." from the Insert menu.
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasXL

..
..
..

"Kurt" <[email protected]>
wrote in message
I have worksheet (destination) in which several cells are linked to a
worksheet (source) in another workbook. My destination worksheet has
not been updated with the latest values in the source sheet, so all
the cells are filled with 0s, etc.

Cell D2 in my destination worksheet is currently linked to V2 in the
source worksheet. I'd like to make E2 be linked to V2. When I click on
V2 (source), choose Copy, click on E2 (destination), and choose Paste
Special > Paste Links, two problems occur:

1. The link is copied but it's copied as $V$2 (whereas all of the
other links don't have the $ sign).
2. The current value in V2 is also copied (i.e., it updates the cell
in my destination sheet when I chose Paste Link). I just want to copy
the link.

I've also tried create the link by starting from the destination
workbook (i.e., type an equal sign in E2, switch to the source
workbook, click V2), press Enter.) Same thing happens.

Suggestions?

(By the way, while I make changes, I've done everything I can to
prevent my destination worksheet from being automatically updated:
Under Tools > Options > Calculation tab, I've checked Manual,
unchecked Recalculate before Save, Unchecked Update remote references,
and unchecked Save external link values.)

 

[Kurt]

Pressing F4 has no affect - the $ is still there.

My understanding is that hyperlinks serve a very different purpose
than using Excel link feature to link a workbook (and the cells within
it) to another workbooks (i.e., Edit > Links ...). As in:
http://office.microsoft.com/en-us/e...age-links-to-other-workbooks-HA001054812.aspx

 

[Jordon]

Press F2 and then F4

Pressing F4 has no affect - the $ is still there.

My understanding is that hyperlinks serve a very different purpose
than using Excel link feature to link a workbook (and the cells within
it) to another workbooks (i.e., Edit> Links ...). As in:
http://office.microsoft.com/en-us/e...age-links-to-other-workbooks-HA001054812.aspx

 

[Kurt]

That did it. The only odd thing is the cell has #REF! in it, whereas
other cells that already had links set up - possibly by someone who
knew what they were doing - all have a non-error default, like 0.0.
(These cells have the same Numeric format and source document as the
cell I created a link for.) The good news is that when I update the
values (F9), the #REF! cell updates with the correct value.

Any idea why it initially sees a reference error?

And when I manually update the cells with the values from the source
document (F9), is there a quick way to de-update the spreadsheet (so I
return to the original, non-updated sheet?) This would be handy for
testing new links to make sure they work. The only solution I've found
is to close and not save the updated version, and then reopen the
file. I was hoping for a quick undo (CTRL+Z) or other shortcut.

Thanks for all your help.