Ask Your Question
0

Linking Cells in Calc 5.0

asked 2015-10-28 03:34:07 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have two spread sheets in Calc. SpreadSheet 1 has stock prices in a list. SpreadSheet 2 takes specific prices from SpreadSheet 1 and performs calculations. I have been using DDE up until now to achieve that but there is a major problem with editing the DDE links in Edit/Links...basically you cannot edit them....well you can..but the edit will not be saved...so you get one off shot...and you cannot even delete the links...this has been a long time bug. Other than DDE, how might I link discreet cells in SS1 with SS2 and in a manner that I can edit and delete the links in the future.

I have just noticed that Edit/Copy cell from SS1 and then Edit/Paste Special/Link no longer works in Calc 5.0 so the DDE link is not even generated. I must be doing something wrong?!

edit retag flag offensive close merge delete

Comments

I changed all my links to the LO recommended way i.e. ='file:///home/abc/Documents/Targets/File1.ods'#$Inputsheet.C14....and when I open this sheet (SS2) I am asked to allow update from File....I ask it to do this....I have since changed all the values in the cells in File1 an am expecting that to carry over to SS2. But none of the values change in SS2. Where am I going wrong...It cannot be this hard. If I press Shift+Ctrl+F9 nothing happens...the old values remain?!?!

dunbrokin gravatar imagedunbrokin ( 2015-10-28 21:55:01 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2015-10-28 12:57:43 +0100

Lupp gravatar image

Suppose your "two spread sheets in Calc" are two different Calc documents in your FileSpace, everyone containing one or more sheets.

I do not understand for what reason you not simply poll values contained in the SourceDocument by the spreadsheet functionality made for that:

In TargetSheet.D14 (eg) of the TargetDocument placing the formula

=SourceDocumentAsURL#SourceSheet.B4

Where B4 simply is an example cell address and SourceDocumentAsURL is something like

='file:///C:/Users/MySelf/Documents/PlayWithMoney/Prices.ods'

on a Win system.

edit flag offensive delete link more

Comments

Sorry, I misunderstood and deleted my last comment...but is what you are recommending just that what I did in my comment above which starts with "I changed.....etc. I have done all that, as you suggest, but the links will not update when I update the source document. I am on Ubuntu.

dunbrokin gravatar imagedunbrokin ( 2015-10-28 22:31:45 +0100 )edit

OK, I have worked it all out...but I am still left with phantom DDE links...how do I get rid of these?

dunbrokin gravatar imagedunbrokin ( 2015-10-28 23:53:19 +0100 )edit

@dunbrokin : Did you already try > 'Edit' > 'Links...'?

Formulae with external links do not update automatically.

Lupp gravatar imageLupp ( 2015-10-28 23:57:44 +0100 )edit

OK, I have now solved the phantom links....however when I follow the suggestion by Lupp, I get #REF! in the cell?!?!

dunbrokin gravatar imagedunbrokin ( 2015-10-29 00:28:10 +0100 )edit

The file must exist. No backslash! The sheet must exist. If its name is syntactically unclean it must be enclosed between single quotes.

I used formula links of that kind often and they always worked.

For a first try open both the documents, and start a formula by = in one cell of the first document. Then simply bring the other document to front with a mousclick and click into a cell containing a value. Enter. Wow!

Lupp gravatar imageLupp ( 2015-10-29 00:36:21 +0100 )edit

In my experience, the poll values contained in the source documents is an inferior method in many ways to DDE and gives me errors such as #REF! which give me no indication of what the problem is! I would much prefer use this method...but it seem inconsistent (though it could be me of course).

dunbrokin gravatar imagedunbrokin ( 2015-10-29 01:23:46 +0100 )edit

The Edit/Links does not work...you have to find the actual formula in the spreadsheet and change it there...and then you are still left with phantom links...the way to get rid of phantom links (coz edit/links does not work for DDE) is to set up real link to match the phantom link...save, reopen and then delete.

dunbrokin gravatar imagedunbrokin ( 2015-10-29 01:27:48 +0100 )edit

Thanks Lupp for taking the interest I will follow your instructions to the letter and report back. Though I have tried doing it already.

dunbrokin gravatar imagedunbrokin ( 2015-10-29 01:29:49 +0100 )edit

='file:///home/pjk/Documents/Targets/stocks.ods'#$Inputsheet.D9 The file stocks.ods exists, the sheet Inputsheet exists...the above is exactly what is my target SS. Both my target SS and source SpreadSheet are open. #REF! appears in the target; the number 2.1 appears in cell D9 of the Input sheet in the Source.

dunbrokin gravatar imagedunbrokin ( 2015-10-29 01:36:13 +0100 )edit

I tried the same with a brand newly minted target spreadsheet and got the same result.

dunbrokin gravatar imagedunbrokin ( 2015-10-29 01:38:02 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-10-28 03:34:07 +0100

Seen: 577 times

Last updated: Oct 28 '15