Linking Cells in Calc 5.0

English
, , ,
#1

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?!

#2

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!!!

#3

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.

#4

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.

#5

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

#6

@dunbrokin : Did you already try > ‘Edit’ > ‘Links…’?

Formulae with external links do not update automatically.

#7

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

#8

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!

#9

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).

#10

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.

#11

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

#12

=‘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.

#13

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

#14

I tried to do the same with a virgin source and target document. Untitled1 and Untitled2. I enter 400 in A1 in Untitled2 and then try to link it to A1 in U1 by hitting = and clicking in A1 of U2…nothing happens. It does not happen!

#15

Ah, of course, it will not work with U2 as U2 has not been saved and hence does not exist! So we can discount that test…but the previous one is still puzzling.

#16

Curiouser and curiouser, while my original source file stocks.ods returns a #REF! now (it did not do so yesterday) my other source file stocks-2x works perfectly. Why is that I wonder…why is stocks.ods being blocked?

#17

Curiouser and curiouser, while my original source file stocks.ods returns a #REF! now (it did not do so yesterday) my other source file stocks-2x works perfectly. Why is that I wonder…why is stocks.ods being blocked?

#18

I can link a cell in stocks to a cell in stocks-2x and it works, the other way around does not work…why I wonder. I cannot get any spreadsheet to link to stocks. Yet I was able to do it yesterday!

#19

Sorry! I’m afraid I cannot help you further without sitting at your side in front of your computer. At least we must focus on one specific problem at a time. And if you report a #REF! error indicator, you should tell if you created the formula pointing with the mouse or entering the extended URL via the keyboard.

I will leave this thread now for awhile.

#20

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.