[Libreoffice-bugs] [Bug 123863] Sumproduct delivers wrong result from calculated external data

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=123863

--- Comment #9 from b. <newbie-02 at gmx.de> ---
Created attachment 159256
  --> https://bugs.documentfoundation.org/attachment.cgi?id=159256&action=edit
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20200402/514b7285/attachment.htm>