Cell value wont update

cwhiston · Newbie Joined: 30 Mar 2006 Posts: 4

When I need a cell to show the value from another cell of a different worksheet, I use '=' in the new cell then click on the old cell and press return. This returns the value of the old cell correctly. If, however, the value of the old cell changes, the new cell does not reflect this; even deleting the formula in the new cell and then re-entering as before still returns the original value. I have the sheet set to autocalculate but have also tried manually recalulating to no effect. The only way I can get the correct value in the new cell is to actually enter it! This can't be right, can it...

RickRandom · Super User Joined: 27 Jan 2006 Posts: 1082 Location: UK

Is your other spreadsheet open? If not, try with it open, and see if it updates.

Also look at Tools->Options, then expand the Calc + symbol, as there may be some options you need to enable.

I seem to recall a similar issue - perhaps search the forum for "update links" or some such text.

Post back with whatever works, or that nothing works, and I (and others no doubt) will think again.

cwhiston · Newbie Joined: 30 Mar 2006 Posts: 4

Both worksheets are open when i try to update value in new worksheet.
I have tried the Tools, Options route and played with various settings but it doesn't make any difference.
I'm still looking to see if there are any postings for this problem; can't beleive that no-one alse has this problem. I was a fairly experinced user of Excel and certainly never this problem there.

cwhiston · Newbie Joined: 30 Mar 2006 Posts: 4

Both worksheets are open when I try to update value in new worksheet.
I have tried the Tools, Options route and played with various settings but it doesn't make any difference.
I'm still looking to see if there are any postings for this problem; I can't believe that no-one else has this problem.
I was a fairly experinced user of Excel and certainly never saw this problem there.

Villeroy · Posted: Thu Mar 30, 2006 10:23 am Post subject:

Your reference looks like ='file://C:/folder/source.ods'#$Sheet1.$A$1.
This kind of reference has to be updated by calling Edit>Links and the source-file needs to be saved. In fact there is a hidden sheet in the target-document. It's named 'file://C:/folder/source.ods'#$Sheet1, so your formulae refer to this sheet actually. Manual update refreshes the content of this hidden sheet. This kind of link is portable. It works with the hidden sheet even when the source file is not accessible.

There is another way to link two documents. It works like this: =DDE("soffice";"C:/folder/source.ods";"$Sheet1.$A$1")
You get this automatically by copying the source and paste-special with "Link"-option checked. This kind of link will update automatically when you save the source-file. It seems to be limited to a few thousand cells (don't know exactly, but tried some time ago). DDE's third param accepts range-names as well. This kind of link stops working when the source file is unaccessible.

cwhiston · Newbie Joined: 30 Mar 2006 Posts: 4

Thanks a lot... I have tried both ways and they work!!
Great stuff.