Created attachment 139282 [details] Example xlsx file to use as external data source Attached spreadsheets were created with LibreOffice 6.0.0.2 and LibreOffice 5.3.3. The main problem here is that functions with complex conditions do not work correctly if we refer to an external file, and we get an Error (504) message in the cell. If we use the ‘SUMIFS’ or ‘AVERAGEIFS’ or ‘COUNTIFS’ function in the local (source.xlsx) file, they work perfectly. Steps to reproduce: 1. Create a new spreadsheet with LibreOffice Calc 2. Click on Tools then choose Options. 3. In the Options set the LibreOffice Calc Formula options just like as you can see at the attached Formula_syntax_setting.PNG file. 4. Fill at least three columns with some simple data. If you want, you could try these functions in the local file, just to make sure they work correctly. 5. Save the file as “Source.xlsx”. This will be the source file which contains the queried data. 6. Create a new spreadsheet with LibreOffice Calc and save as “Function-Error-504.xlsx”. (This will be the reference file) 7. In the “Function-Error-504.xlsx” file create a correct SUMIFS or AVAREGEIFS or COUNTIFS function which refers to the “Source file” data. Here is an example code for a SUMIFS function: =SUMIFS('file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.C1:C5;'file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.B1:B5;'file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.B1;'file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.A1:A5;'file:///E:/Functions-Error-504/Function-Error-504-Source.xlsx'#$Munkalap1.A1) 8. Click Ok in the Function Wizard. 9. Save and reload the file. Actual results: We get an Error (504) message in the cell for these functions. Even if we set only one condition in the “more conditional” functions, we got the same error. Other similar functions (SUMIF, AVERAGEIF or COUNTIF) work well if we use these steps. These problems may (or may not) be the same as the VLOOKUP/HLOOKUP function bug #114820. Expected results: We shouldn't receive an error code in the cell, because the function is correct and works perfectly in the local files. Verzió: 5.3.3.2 Build az.: 3d9a8b4b4e538a85e0782bd6c2d430bafe583448 CPU szálak: 4; Rendszer verziója: Windows 6.2; Felületmegjelenítés: GL; Elrendezésmotor:új; Területi beállítások: hu-HU (hu_HU); Calc: group Verzió: 6.0.0.2 Build az.: 06b618bb6f431d27fd2def25aa19c833e29b61cd CPU szálak: 4; OS: Windows 6.1; Felületmegjelenítés: alapértelmezett; Területi beállítások: hu-HU (hu_HU); Calc: group
Created attachment 139283 [details] Example file with function references to the external data source file
Created attachment 139284 [details] The two files side by side. Referring to the data source gives error.
Created attachment 139285 [details] The example xlsx file to use as external data source - saved by LO 5.3.3
Created attachment 139286 [details] Example file with function references to the external data source file - saved by LO 5.3.3
It looks like a dup from bug 114820, only with different functions.
(In reply to MM from comment #5) > It looks like a dup from bug 114820, only with different functions. Sure, but I think it's better to file a dupe than adding more and more similar problems to a single huge bug. Also, the feature works for some functions and not for others, this makes it hard to decide how specific this is.
Review: https://gerrit.libreoffice.org/#/c/49264/
*** Bug 94309 has been marked as a duplicate of this bug. ***
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b6db65752c60de7268e4f65d7fc5ac110adb2fb0 tdf#115162 Fix support for external reference for IFS functions It will be available in 6.1.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "libreoffice-5-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d4a41817aa86fffaa3a27265966bc8265d34dd0a&h=libreoffice-5-4 tdf#115162 Fix support for external reference for IFS functions It will be available in 5.4.6. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d9fafddcf8eb37916c8a8e76af36d67c8db84a98&h=libreoffice-6-0 tdf#115162 Fix support for external reference for IFS functions It will be available in 6.0.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/859a7e52b20859a5c13ce83eeb7ec7fceb126fb5 tdf#115162: sc_uicalc: Add unittest It will be available in 7.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.