Created attachment 158347 [details] zip file containing 7 calc worksheets illustrating link problem I have a group of spreadsheets, one for each "month", where the value of a cell in a given month is intended to be set equal to a value in the previous month, but the link sometimes returns an incorrect value. The attached zip file contains 7 such spreadsheets to illustrate the problem. In the attachment, the September sheet is not linked to any other sheet, and successive months sheets are linked to the previous month. There are sheets attached for September though March. Note that the link for the October through February sheets returns the correct values from the previous month's spreadsheet but that for March returns 0 when the correct value is 160. The problem cannot be corrected by "updating" the link. This problem has proven very difficult to replicate and sometimes disappears (or appears) if the associated files are moved to a different directory.
Could you give a try to last stable LO version 6.3.5? Indeed, perhaps it's already fixed.
I've tried release 6.4 RC3 as discussed in here: https://ask.libreoffice.org/en/question/231827/link-to-external-file-in-lo-calc-returns-incorrect-data/ The same error occurs with 6.4. If you think it would be helpful to try 6.3.5, I will do so. Please advise.
When I opened & updated the sheets in order starting from Sep 2019, there was no problem. Is this how you do it? This is how I expect you would have to do it. I can see the problem, if I do not open them in order like this. Version: 6.4.3.2 (x64) Build ID: 747b5d0ebf89f41c860ec2a39efd7cb15b54f2d8 CPU threads: 4; OS: Windows 10.0 Build 18362; UI render: default; VCL: win; Locale: fi-FI (fi_FI); UI-Language: en-US Calc: threaded
If you open the files in order, you will not see the problem. My expectation is that if I open the current month's file, it will fill the destination cell on that file with whatever has been saved to the source cell in the previous month's file, even though that value is dependent on it's source, which could have changed, etc. That expectation was born of three years of experience with OpenOffice. I stopped using OpenOffice some time ago because its performance had deteriorated significantly. LibreOffice performed much better and "linking" behavior was as I had come to expect with OpenOffice. At some point in the past six months or so, that behavior changed. Here is a little more background, in case it's helpful: I've built a rather complex spreadsheet for my sister-in-law to track attendance for a women's club. She is not computer-savvy, so I've tried very hard to hide complexity from her while accounting for some of the crazy rules that are used by the club to "excuse" absences and predict needs for lunches tables, and chairs for their monthly meetings. When I used OpenOffice, when I told it to update links upon opening a file for a month, I didn't see what it was doing in that process, but, as I've said, I always got the results I expected. It took whatever had been saved in the previous month and put its value in the appropriate linked cell in the current file. When I switched to LibreOffice, if I opened the file for, say January, it asked if I wanted to update the links (there are many other links between months), and when I said yes, it proceeded to ask me if I wanted to update links for all previous months; i.e, December, November, October, and September (the year starts in September). I concluded from that that LibreOffice actually opened all preceding months in the course of opening the current one. I suspect that it's getting "hung up" in the cascading dependencies rather than simply copying the value I want transferred to this month's file. I suspect a way to "fix" this problem is to have my sister-in-law open the current month and all preceding months, in the correct order, in order to ensure that the value used for the current month is correct. Either that or I'll have to write her a macro that does that for her. That seems like an awful lot of work to do to recover functionality that I've had for at least three years in OpenOffice. If you or anyone has a better solution, I'd very much like to hear it. The above said, I actually tried to fall back to OpenOffice, but guess what? It now behaves the same was as LibreOffice does! I'd like to be able to continue to use LibreOffice if I can get it to behave how I expect it to. Meanwhile, I've begun to port the application to Excel. I'm hoping you can save me from that task! Thank you for your support.
(In reply to Ed from comment #4) > The above said, I actually tried to fall back to OpenOffice, but guess what? > It now behaves the same was as LibreOffice does! I opened Mar 2020 in LibreOffice 3.3, the first version ever released (contains everything found in OpenOffice.org at the time) and B2 shows the value as 2. Same result with versions 3.5.0, 4.4.7 and 5.4.0. The chained updating you speak of sounds cool, but I am unable to find a version where it worked.
Go to Tools - Options - LibreOffice Calc - General - and activate Update links when opening to be "Always". Maybe it works
(In reply to BogdanB from comment #6) > Go to Tools - Options - LibreOffice Calc - General - and activate Update > links when opening to be "Always". Maybe it works I tried this. It also requires you to go to Tools - Options - LibreOffice - Security - Macro security - Trusted Sources - Trusted File Locations, Add - Select directory. It does not help, the (non-)effect is the same as clicking the button to update link.
Hello Buovjaga, Thank you for staying with me. Let me try to be more precise. If I set the value of a cell in spreadsheet A equal to a value of a cell in the "linked" spreadsheet B, I expect that value to show up in the destination sheet without having to open a bunch of other sheets. I don't care if the source cell value is dependent on values in other sheets. There is a value in the source sheet, and that's the value I want to show up in the destination sheet. This used to work for me in OpenOffice. Now it does not work in OpenOffice or LibreOffice. Are you telling me you've tried it, and it doesn't for you? I am absolutely certain this is the way the linking functionality used to work for me. Something has changed. Maybe I'm doing something wrong. Is there a way to set the value in a cell in a spreadsheet to the value that is in a cell of another sheet? Again, I don't care about how that source value is defined or indeed if it's "correct". If it's not, I'll fix it some other way. I'm not expecting, nor do I want, any "cool" chained updating. That was just my attempt to try to explain what I thought was going on.
Please read about DDE link. https://help.libreoffice.org/6.4/ro/text/shared/01/02180000.html?DbPAR=SHARED#bm_id3156156
(In reply to BogdanB from comment #9) > Please read about DDE link. > > https://help.libreoffice.org/6.4/ro/text/shared/01/02180000. > html?DbPAR=SHARED#bm_id3156156 I've been there and I can't get the expected result using DDE links. Moreover, when this functionality worked in the past, I did not use DDE links either. I struggled with this for a very long time before coming here.
(In reply to Buovjaga from comment #5) > (In reply to Ed from comment #4) > > The above said, I actually tried to fall back to OpenOffice, but guess what? > > It now behaves the same was as LibreOffice does! > > I opened Mar 2020 in LibreOffice 3.3, the first version ever released > (contains everything found in OpenOffice.org at the time) and B2 shows the > value as 2. > > Same result with versions 3.5.0, 4.4.7 and 5.4.0. The chained updating you > speak of sounds cool, but I am unable to find a version where it worked. If I open, save, and close successively September, then October, November, December, the value if B2 is correct for each month. Then if I open January, the value is -1. It should be 158. This makes absolutely no sense to me. Of course, succeeding months are then incorrect as well. If after the above, I repeat the same sequence of opening, saving, and closing with OpenOffice 4.1.3, then the correct values get posted to cell B2 for each month.
On pc Debian x86-64 with master sources updated today or with LO Debian package 6.4.4, here what I did: - uncompress the whole zip in a directory - open "Jan 2020 Sign-In Sheet.ods" => warning for macro and it asks me to enable or disable - I chose "disable" => a warning in top of grid appears telling: "Automatic update of external links has been disabled" + button "Allow updating" 1) If I don't click button, B2 cell is empty 2) If I click on it, after 2/3 seconds, B2 value is 158
(In reply to Ed from comment #11) > If I open, save, and close successively September, then October, November, > December, the value if B2 is correct for each month. Then if I open > January, the value is -1. It should be 158. This makes absolutely no sense > to me. Of course, succeeding months are then incorrect as well. Now these were useful steps. I was able to fix the links with an older version. Then, I could test with different versions by always starting from October. I had bad luck with bibisecting, though. It seemed to appear in 6.1, but with the Win 6.1 repo I got a bogus result. I used this sort of command to make the bibisecting quicker: rm instdir/cache/opengl_device.log; git bisect start master oldest && instdir/program/soffice 'z:\Illustration of Calc link problem\Oct 2019 Sign-In Sheet.ods' && instdir/program/soffice 'z:\Illustration of Calc link problem\Nov 2019 Sign-In Sheet.ods' && instdir/program/soffice 'z:\Illustration of Calc link problem\Dec 2019 Sign-In Sheet.ods' && instdir/program/soffice 'z:\Illustration of Calc link problem\Jan 2020 Sign-In Sheet.ods' Hopefully someone else has better luck.
Has any additional work been done on this? The last update was over a year ago and there have been multiple versions of LibreOffice released during that time.
Like Julien => warning for macro and it asks me to enable or disable - I chose "disable" => a warning in top of grid appears telling: "Automatic update of external links has been disabled" + button "Allow updating" After that, No repro, all links are working, with this version of Libre Office: Version: 7.6.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: f97a7b965be154824e108802b386e3132b0a237a CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: fr-FR Calc: CL threaded