USE OF F9 key

CHEECHBURGER · September 10, 2020, 10:35am

i have attached two calc workbooks - named untitled 3 and untitled 4. I used copy - paste - link to get the formula to get data from untitled 3 into untitled 4. the { } symbols surround the formula. when data is changed in untitled 3, the sum does not go to untitled 4, if i hit f9 it does.

in tools, macro security, trusted sites the library containing the two workbooks has been added. i can send screen image to show this. the other entry to recalculate when opened in the tools options sections was also created.

i have tried saving the files in ODS format, ODS macro enabled format XLSx format - is there another format i should be using. is this a bug and i have to constantly hit F9?

WHY doesn’t it automatically recalculate

Untitled 3.xlsx file name Untitled 4.xlsx C:\fakepath\Screenshot (7).png

anon73440385 · September 10, 2020, 11:53am

i have attached two calc workbooks

Where?

CHEECHBURGER · September 10, 2020, 12:03pm

i have edited with the workbooks and screen shot

anon73440385 · September 10, 2020, 12:44pm

WHY doesn’t it automatically recalculate

I have tried to explain (draped in a question) in my comment to this question that you obviously expect the impossible. File Untitled 3 doesn’t know, which other files are referring to any of its cells/ranges. So how should Untitled 3 trigger a change in Untitled 4?. On the other hand Untitled 4 doesn’t know anything, whether an external file has been changed (this would require a permanent LibreOffice internal process watching the files status for file changes on a system level). Hence Untitled 4 is not able to automatically update any cell content referring to external data. Therefore the only methods are:

check at some time interval on Untitled 4 for new values in the external data sources (i.e in Untitled 3 (which wouldn’t work in your case, since you link to formulas and not values) or
update manually

anon73440385 · September 10, 2020, 1:23pm

…continued
A manual update could be triggered on opening Untitled 4, if Tools -> Options ->LibreOffice Calc -> General -> Section: Update links when opening -> Option: [o] Always (from trusted locations) is set.

erAck · September 10, 2020, 2:06pm

If you expect data in Untitled3.xlsx to be updated whenever the linked data in Untitled4.xlsx is changed while both documents are simultaneously open without having to trigger a manual update then use a DDE() function like this:

=DDE("soffice";"/home/user/Untitled 3.xlsx";"'PAY DETAILS'.B15:D15")

entered as array formula (Shift+Ctrl+Enter) and then make sure that under Edit → Links to External Files… for that file Update is set to Automatic.

Obviously instead of /home/user use the actual path on your system, it has to be the full path, relative paths do not work and the documents can’t be moved around without changing the path string.

CHEECHBURGER · September 11, 2020, 11:30am

erACK
thanks for your answer. you clarified the DDE formula. unfortunately I am not up to speed in LibreOffice and cannot complete the parameters using the DDE wizard. “soffice” means nothing to me. finding the path, well i’ll spend the day finding it. I get a 509 non descriptive error. Thanks for your help. I wish I could find someone in the USA who I could pay to write the formula for me that would work on my pc
ralph

erAck · September 11, 2020, 2:35pm

soffice is the DDE server application name, here for LibreOffice (on Windows some other programs can also act as DDE servers). The document path in your case, judging from the screenshot, could be one of

C:\Users\ralph\Documents\...

listed under Trusted File Locations, or wherever the source document resides.

I don’t get what you mean with “DDE wizard”.