USE OF F9 key

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.xlsxfile nameUntitled 4.xlsx C:\fakepath\Screenshot (7).png

i have attached two calc workbooks


i have edited with the workbooks and screen shot

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

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.

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.

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

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


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

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

See also DDE function help.

Err:509 is “Missing operator” (you can see in the status bar when the cell cursor is on the error cell), see also the list of error codes. You may have forgotten to put the strings in double quotes or have another typo in the expression.

hi erACK
the wizard i referred to was the Fx dialogue box. with your information i got the formula DDE to work. YOU - yes you - helped me so much with your patient and detailed explanations. I have been struggling with conversion from windows 8 to 10 while at the same time excel to LibreOffice. You saved my bacon. thanks so much

Glad to help. Then please just mark the answer as correct by clicking the check mark. Thanks.