Insert blank spreadsheet in text document and link calculations

[GBH]

I am trying to link some things within a writer document as to calculations. Here is my probably very simple question (and I am probably an idiot for not getting it to work):

(1) In a text document I create a calc spreadsheet in it by choosing Insert>Object>OLE Object>LibreOffice Spreadsheet- Create New>

I am not pulling from and do not want to insert external spreadsheets. Then, in that spreadsheet I will put in various formulas and do various calculations.

(2) Later in the document I do the same thing with another spreadsheet object with other calculations.

I want to be able to link the results from the calculation in the first spreadsheet above (in 1) to be used in the calculations in this worksheet (2), and if I change the numbers in first one, it will update and change the calculations in the second one also (and maybe also others I may create elsewhere in the document).

I am using the Insert Link and it does not seem to work (and have also tried that plus Select All). I get an error of Not a Valid Reference.

If I did this in a Microsoft Word document doing the same thing with new Excel sheets using the Insert link command it would cause the later linked excel object to be updated. However, this is not happening here and I need some help.

[Lupp]

That’s a funny question, and it’s hard to imagine a realistic use-case.
Maybe that’s the reasons for what it is interesting.

Well, I think you can’t - for the simple reason that the “other” embedded document you want to link to has no URL.
However, I played around a bit, and I can tell you that, resorting to user code, it’s actually possible to push contents from one embedded spreadsheet document to the other one, which then will recalculate…

A really funny trick from sorcerer’s grimoire.

[GBH]

Thanks for the thoughts- there is a very useful business case for it!

However, I am not sure you answered the right question. I am not using hyperlinks. After I copy the cell in the first spreadsheet I’m then opening the next one in the document and clicking Paste Special and it has a whole bunch of choices (Paste all, Text, Numbers, Date and Time, Formulas, etc. as well as Link). I am using Link. I realize this is not MS Word, but when I use the same choices in Word and select Link it links the items in the two Object Excel sheets I created new in the word document.

The link you are referring may not have to do with embedded spreadsheets.Try creating a writer document. Click and create insert new object, create new spreadsheet. Put a number in it. Create another new object, new spreadsheet in same doc. Copy the number in the first sheet and then click Paste Special and then Insert link into the second sheet. If I change the number in the first sheet I want it to change in the 2nd.

[GBH]

With more space I can tell the business reason. I have huge business documents. Each is unique and has over 100 pages. There are lots of individual sheets in the document (presented as tables to the reader) with calculations and which tie to other sheets in the documents with other calculations. If I change a number in one I want it to update all of the subsequent calculations in other sheets throughout the document. We do not want to do all of this in an external spreadsheet and then insert them for a lot of reasons that are too lengthy to go into here. We’ve used LibreOffice and predecessors for years, but miss the ability to use the Paste Special command and then Link when we used Word to update calcs throughout and were hoping there was some way to do it in LibreOffice.

[Lupp]

My comment was about spreadsheet documents OLE-embedded into a Writer document,
And -as far as I understand- any linking (like you try it) will require something technically acting as an URL (URI).
I haven’t access to MS Word, but I would be interested to open an MS-Word document with two linked OLE-Excel “workbooks” with LibO experimentally.
You may upload one using the paperclip-tool when editing your original question.
Concerning a hypothetical enhancement request I would prefer a way offering a more complete UI for the OLE spreadsheet, and there allowing for the creation of additional “Window” (as feasible in the non-OLE Ui, but instantiating the next “window” as a next OLE-frame.

[Lupp]

BTW: The .odt you save a doument to will write the persistent representation of the sheets document To a logical subfolder. However, I don’t know how to use the respective structure like in an URL. (If I remember correctly there once were internal URL in LibO used for images.)
Concerning OLE-Calc-sheets there is the strange fact that they are stored as V1.1 objects, and that, when opening such an object for editing from Writer the first time since loading, an alert concerning the “software other than LibreOffice” is raised. (This is about an OLE sheet created from LibO 5.4.3.2 in Writer.)

[GBH]

Thanks much. Stay healthy.