Calc does not update all external links if user defined conditional cell formatting ([>0]General;[<0]General;"";"") is used. The purpose of this formatting is to prevent links to empty external cells displaying as zero. This bug appeared in LO 4.4.0.0.beta1 and has existed since. Unfortunately, I can't identify the change that caused it. https://wiki.documentfoundation.org/Releases/4.4.0/Beta1 I have used external links in the way: ='file:///C:/LinkTest/TargetFile.ods'#$Sheet1.A1 This is the same syntax that LO uses to create links, when starting a formula and then clicking a cell in a another spreadsheet. However, if the external link is done by copying the cell in the external spreadsheet and then pasteing it as a link through the "paste as special"-dialog, LO ads curly brackets before and after the formula. {='file:///C:/LinkTest/TargetFile.ods'#$Sheet1.A1} These type of links work correctly with the user defined formatting, but LO updates these link without asking the user. I will attach three files to demonstrate the bug. First download all files to the same folder (ie. C:\LinkTest\) TargetFile.ods = Cells in this file are referenced from the other documents. LinkTestUnFormatted.ods = When opening the file, LO ask updating the links and updates them correctly LinkTestFormatted.ods = When opening the file, LO ask updating the links, but updates only certain cells.
Created attachment 115774 [details] File that is the target of external links from other files.
Created attachment 115775 [details] Unformatted file that has working external links
Created attachment 115776 [details] File that has cell formatting that prevents some external links from updating properly
Hi devseppala at gmail dot com, you wrote: >LO updates these link without asking the user. if your TargetFile.ods is open then LibreOffice modifies all values in LinkTestUnFormatted.ods and the second group of cells (A8, A9 and A10) in LinkTestFormatted.ods, it does it anyway and before the user answer. If TargetFile.ods is closed then no value is silently updated before the user answer (in both cases). I tested with LibreOffice 4.4.3.2 on Windows XP service pack 3, my operating system and LibreOffice use italian language.
#Zangune: Thanks for looking into this issue. I didn't realize that the state (opened /not opened) of the TargetFile affects how the links are updated. I must have confused that with the "silent updating" of the links with curly brackets. Anyway, I think this behavior can be dangerous and LO should ask about updating links regardless if the target file is opened or not. Just to clear things, do I understand correctly that you can confirm that the first group of links (with the conditional formatting) in the LinkTestFormatted.ods file do not get updated correctly?
If TargetFile.ods is open then links in the the second group are updated silently, not the links in the first one.
I can not confirm with LO 4.4.3, win7 When open LinkTestFormatted.ods = When opening the file, LO ask updating the links and updates all cells: 11 12 13 14 18 19 20
raal, first open TargetFile.ods, then open LinkTestFormatted.ods. LibreOffice will ask you if it will have to update links or not, *before* answering check all cells, what do you read? My results: A1 = 61 A2 = 42 A3 = 43 A4 = 44 A8 = 18 A9 = 19 A10 = 20 Cells from A1 to A4 are OK, they are not updated silently. Cells from A8 to A10 are *not* OK, they are updated silently. After clicking OK all cells have the correct values: A1 = 11 A2 = 12 A3 = 13 A4 = 14 A8 = 18 A9 = 19 A10 = 20
@raal, When you opened LinkTestFormatted.ods file, did you have the TargetFile.ods file open. If I have it opened at the same time I open the LinkTestFormatted.ods, all values are updated correctly. However, if I open only the LinkTestFormatted.ods file, LO updates only the first value in the first group. Like this: A1 = 11 A2 = 42 <-- not updated A3 = 43 <-- not updated A4 = 44 <-- not updated A8 = 18 A9 = 19 A10 = 20 I have tested this with the same platform as you, LO 4.4.3 win7 @Zangune, I originally wrote this bug report to address the issue that LO does not update all the values in the first group of cells. As in the example above. Should this issue of silent updates be split to a separate bug report.
devseppala, you wrote: >Should this issue of silent updates be split to a separate bug report. Is this a question? Is this a recommendation? Anyway this problem and my observation may be related to #90204
(In reply to Zangune from comment #8) > raal, first open TargetFile.ods, then open LinkTestFormatted.ods. > LibreOffice will ask you if it will have to update links or not, *before* > answering check all cells, what do you read? > > My results: > > A1 = 61 > A2 = 42 > A3 = 43 > A4 = 44 > > A8 = 18 > A9 = 19 > A10 = 20 > > Cells from A1 to A4 are OK, they are not updated silently. > Cells from A8 to A10 are *not* OK, they are updated silently. > > After clicking OK all cells have the correct values: > > > A1 = 11 > A2 = 12 > A3 = 13 > A4 = 14 > > A8 = 18 > A9 = 19 > A10 = 20 Yes, same results, I can confirm this behaviour
(In reply to devseppala from comment #9) > @raal, When you opened LinkTestFormatted.ods file, did you have the > TargetFile.ods file open. If I have it opened at the same time I open the > LinkTestFormatted.ods, all values are updated correctly. However, if I open > only the LinkTestFormatted.ods file, LO updates only the first value in the > first group. > > Like this: > > A1 = 11 > A2 = 42 <-- not updated > A3 = 43 <-- not updated > A4 = 44 <-- not updated > > A8 = 18 > A9 = 19 > A10 = 20 > > I have tested this with the same platform as you, LO 4.4.3 win7 > > @Zangune, I originally wrote this bug report to address the issue that LO > does not update all the values in the first group of cells. As in the > example above. Should this issue of silent updates be split to a separate > bug report. I can not confirm this: When I open LinkFileFormatted.ods (targetFile.ods closed), click "update data" , then I have all values updated 11 12 13 14 18 19 20
Works for me with: Win10x64 Version: 5.2.2.2 (x64) Build ID: 8f96e87c890bf8fa77463cd4b640a2312823f3ad CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; Locale: es-ES (es_ES); Calc: CL