Created attachment 149735 [details] The attached to files are "BUG SUMPRODUCT" (open this and have the link updated automatically or manually) and "BUG External Data". Summary: The attached example shows that under the given conditions SUMPRODUCT fails: • Data is from an external table • Data in that table is calculated • Data is refreshed manually or automatically Setup: The attached tables are one data table that feeds content to our test table. I use SUMPRODUCT which claims a certain word is found zero times. Below I do the same comparison but with IF and it works and the SUM over the IF also works. What you see: C1 is (after updating the table) 0 but should be like C2 8 Conditions to narrow the problem down: The following changes make SUMPRODUCT work correctly: • If the linked data is replaced by the same context but as text instead of formula • !!! Running SUMPRODUCT manually after the connections have been updated (copy the Formula from C1 into C1 and the result is correct again) !!! I hope with all of this the bug is trivial to find!
i can confirm this with: Version: 6.1.5.2 (x64) Build ID: 90f8dcf33c87b3705e78202e3df5142b201bd805 CPU threads: 4; OS: Windows 10.0; UI render: default; Locale: de-DE (de_DE); Calc: steps to reproduce: - open "BUG SumProduct.ods" - cell C1,C2 values are 8 - press [Enable Content] - cell C1 is 0 while C2 is 8 - Ctrl+Shift+F9 (hard recalc) does not update - save & reload file with "Tools/Options.../LibreOffice Calc/Formula" Recalculation on File Load -> ODF Spreadsheet [Always recalculate] - cell C1 is 8
Seems the issue is in relation with the name of the linked sheet. Changing Stückliste by Stuckliste and a hard recalc apparently solves the issue
(In reply to m.a.riosv from comment #2) > Seems the issue is in relation with the name of the linked sheet. > Changing Stückliste by Stuckliste and a hard recalc apparently solves the > issue i can not confirm it, can you attach an example?
I did with the file attached by the reporter. Changing sheet name 'Stückliste' by 'Stuckliste' (ü -> u) and doing a hard recalc and works for me. Version: 6.2.1.2 (x64) Build ID: 7bcb35dc3024a62dea0caee87020152d1ee96e71 CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: es-ES (es_ES); UI-Language: en-US Calc: threaded
(In reply to m.a.riosv from comment #4) > I did with the file attached by the reporter. > Changing sheet name 'Stückliste' by 'Stuckliste' (ü -> u) and doing a hard > recalc and works for me. hard recalc worked for me without changing the sheetname as follows: - open "BUG SumProduct.ods" - do *not* press [Enable Content] - Ctrl+Shift+F9 (hard recalc) - cell C1 is 8 but *not* if i press [Enable Content] - open "BUG SumProduct.ods" - press [Enable Content] - Ctrl+Shift+F9 (hard recalc) - rename sheet to 'Stuckliste' - Ctrl+Shift+F9 (hard recalc) - cell C1 is 0
I have tried the steps with the latest 6.2.2: 1. Bug still exists. 2. is independent of any renaming 3. only happens if content is updated 4. hard recalc works updates correctly only if 3. is not given So the work around for us is: • open document • update content • save & close document • open document (don't update) • hard recalc shift+ctrl+f9 It's quite time consuming for our document as they are big and load slowly, so I really hope the bug is quickly resolved (please)
Created attachment 156802 [details] external_data for working sample i've build you a sample that shows (observe red bordered cell on sheet1): (data file here, calcsheet in next comment,) 'linking in' external data can work even with: • Data is from an external table • Data in that table is calculated • Data is refreshed manually or automatically try to find the difference towards your sheet, did you pull in the data in ranges? (i took the full sheet), did you pull in the data as html? (i tried as copy and as link), as such conditions are hard to analyse or reverse, i'd like if you can provide a step by step script to produce a failing version with small amounts of data ... i could not! repro your suggestion that after replacing the external data by copied text (of itself) the formula would work, neither that it'll work by replacing C1 with a text copy of itself ... :-(
Created attachment 156803 [details] calculating sheet work working attempt ... use together with datafile from prev. comment ... steps as in OP.
Created attachment 159256 [details] files_to_illustrate_possible_solution rechecking: short: i assume an error in the files / the link, there is no 'Element' shown in 'edit - links to external files' dialog box 'edit links', and a named range 'StücklisteImportData' in the data file has #REF! error, @Christoph: there is something 'special' with your file, with the link in it ... if you rename the external file and activate updating of content - thus not finding the file - the sheet 'Stückliste' is empty, except the area V3:V1003. in the cells (input line) stands '=NA()', the result is shown as #N/A, if you insert a new sheet and create a new link on that sheet to an external data file - copy of yours, you need a range there to reference to, i couldn't check which you'd choose thus created a new one covering the whole sheet 'Stückliste' in the data file - then linking, updating and calculating works as expected, see attached sample *_var_a, cells G1 and I1, thus imho there isn't a general fault with special characters " " in file names, special characters "ü" in sheet names, a #REF! error in col. Q (all things to better avoid, but they do work in this case), nor with 'sumproduct' in LO or a special version of LO, but something broken in the way the data file is linked in in your special case ... i assume you once had a named range 'StücklisteImportData' in your data file, actually it has an #REF! error, assigning an area to that range solves the problem ... it's somewhat odd how theese things are implemented in calc, it looks as if you can link to different parts of a sheet, without a possibility to check where you linked, have multiple links in one sheet, even overlapping, have two menu entries 'edit - links to external files' and 'sheet - link to external data', all in an attempt to make it universal in creating, but with small capabilities to check or edit, if you or i had spotted 'edit - links to external files' showing an empty space besides 'Element' at the bottom of the dialog box ... that might have been the hint for a faster solution ... will set to 'notabug' in some time if nobody objects, reg. b.
sorry, typo in prev. comment, not 'StücklisteImportData' has #REF! error, but 'StuecklisteImportData' with 'ue', rechecked, starting with files provided by OP: check / set language settings locale: german, open file 'BUG SumProduct.ods', check edit - Links to External Files: no 'Element:' assigned in footer area, break link, open file 'BUG External Data.ods', check ctrl-F3 - Manage Names: StuecklisteImportData has #REF! error, delete it, open sheet Stückliste, select all (ctrl-a), type StuecklisteImportData in 'name box', check ctrl-F3 - Manage Names: StuecklisteImportData now $SStückliste.$A:$AMJ, save file, change to 'BUG SumProduct.ods', select tab 'Stückliste', select all (ctrl-a), sheet - Link to External Data: browse to data file, open, select 'StuecklisteImportData' from Available Tables/Ranges, change to sheet 'Übersicht', check C1: 8, thus calc, 'sumproduct' and calculation correct, link was broken, setting 'NOTABUG'