Description: It's about doing a lookup in an external .ods file from a main .ods file. A testcase with 2 simple files is included that demonstrates the problem. A series of 4 screenshot images gives a quick overview of the problem. Posted it in other forum first. Person there says it might be a bug. See: https://ask.libreoffice.org/en/question/159779/how-to-do-this-special-lookup-in-external-file/ Not sure if this is a bug -or- The formula is just wrong -or- This mechanism is not possible with LibreOffice Steps to Reproduce: 1. Store the 2 .ods files in the same directory. 2. Open the main .ods file. 3. Do the things that you see in the screenshot images. 4. (The cell where the problem occurs is marked yellow/orange) Actual Results: Lookup in the same file works without problems. Lookup in the external .ods file doesn't work (this way). Expected Results: Lookup in the external file should be possible in a similar fashion. Reproducible: Always User Profile Reset: Yes Additional Info: Not sure if possible with a slightly different formula. People in the ask.libreoffice.org say it may be a bug. See: https://ask.libreoffice.org/en/question/159779/how-to-do-this-special-lookup-in-external-file/ I have a tandem of 2 .ods files - one that holds a lot of data and one that is the interface. C:\fakepath\problem_2_main_file.ods C:\fakepath\problem_2_external_file.ods Usually I use "Data"->"Validity..." and select "Cell range" and use a formula like the following: IF($data.$B$1:$B$9999=B6,$data.$C$1:$C$9999) This formula checks creates a list of things to select from (column "C" in "data") by filtering only those where column "B" matches a certain category. I try to use a similar formula when looking up in the external .ods file, but this formula fails: IF('file:///E:/Problem2/problem_2_external_file.ods'#$external_data.B$1:B$9999=B14,'file:///E:/Problem2/problem_2_external_file.ods'#$external_data.C$1:C$9999) The following screenshot images illustrate the problem. I will try to do this with Microsoft Office since I'm out of options now - if it works there, then it should(!) work in LibreOffice too. Maybe the syntax of the formula is wrong? Or perhaps I did not think of something else? One alternative might be to use a real database and a C++ app or something like that. But I want to use two Calc files instead - one that holds the data and one that is the user interface. My next workaround would be to add the relevant data to the main file - which is a problem. You see, the idea was to separate the 2 files because the database is a big file (10 MegaByte) whereas the other file that uses this data is only 100 KiloByte in size and will be used like a 1000 times to represent a 1000 times the data in different combinations. Thus, I would have 1001 x 10 MB versus 1000 MB + 1000 x 0,1 MB which means 1000 times more disk space used when having the data all in one file. Also ... I separated the two because if things change in the database then only the database will need an update. The other 1000 files which reference the other .ods file won't need an update. The data .ods is located in a folder above the interface .ods file.
Created attachment 143330 [details] simple testcase - first (main) .ods file
Created attachment 143331 [details] simple testcase - second (external) .ods file
Created attachment 143332 [details] 1st screenshot (1 of 4)
Created attachment 143333 [details] 2nd screenshot (2 of 4)
Created attachment 143334 [details] 3rd screenshot (3 of 4)
Created attachment 143336 [details] 4th screenshot (4 of 4)
Created attachment 143337 [details] Sample showing the issue on the IF function issue with array to external file for the IF function on THEN or OTHERWISE (Using the same linked file)
Version: 6.2.0.0.alpha0+ Build ID: 67c88e284af74c88e37cc8f66cdfc0e346de45ac CPU threads: 4; OS: Windows 10.0; UI render: GL; TinderBox: Win-x86@42, Branch:master, Time: 2018-06-19_23:41:55 Locale: es-ES (es_ES); Calc: CL
Reproduced back to Version: 4.1.0.0.alpha0+ Build ID: 64dafbe584fe7644ec29b96b6a9a9588ba4619b @Eike, @Winfried, I thought you could be interested in this issue...
This boils down to an external reference not being supported in the THEN or ELSE paths of an IF jump if in array/matrix mode. Short reproducer: =IF('problem_2_external_file.ods'#$external_data.B$1:B$9=B14,'problem_2_external_file.ods'#$external_data.C$1:C$9) entered in any cell of problem_2_main_file.ods as array formula (closed with Shift+Ctrl+Enter) for the preselected fruits case in B14 displays the first 5 values as Err:502 (which should be the matching 5 fruits) followed by 4 values FALSE (as there are no more matches for fruits in the range). Taking.
A workaround is to not have the external reference be the final IF result, introducing an operation such as concatenating with an empty string (...&"") in this case helps: =IF('problem_2_external_file.ods'#$external_data.B$1:B$9=B14,'problem_2_external_file.ods'#$external_data.C$1:C$9&"")
(In reply to Eike Rathke from comment #11) > A workaround is to not have the external reference be the final IF result, > introducing an operation such as concatenating with an empty string (...&"") > in this case helps: > > =IF('problem_2_external_file.ods'#$external_data.B$1:B$9=B14, > 'problem_2_external_file.ods'#$external_data.C$1:C$9&"") Confirmed, the workaround works for me. Thanks for the idea! :-)
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a5b9bb7be70c7aec2388199e68a0cf86dd220955 Resolves: tdf#118561 handle external references as jump matrix result It will be available in 6.2.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=57e908136f4b7135e6c4a35a81e45c47f9f1b548&h=libreoffice-6-1 Resolves: tdf#118561 handle external references as jump matrix result It will be available in 6.1.0.2. 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.
Pending review for 6-0 https://gerrit.libreoffice.org/57148
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8f3515b5e54dabb08ddb01253d9503e9c16cfebe&h=libreoffice-6-0 Resolves: tdf#118561 handle external references as jump matrix result It will be available in 6.0.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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/a36455c8d1aba28ca593a90f2957078934ea5700 tdf#118561: sc_subsequent_filters_test: Add unittest It will be available in 7.1.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.