Bug 115162

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