Description: For instance: =LOOKUP(A38;'file:///M:/produktion/ElectrodevArtNr.ods'#$Artikelregister.A$7:A$110;'file:///M:/produktion/ElectrodevArtNr.ods'#$Artikelregister.E$7:E$110) This can work in one cell but not in another. I have tried to enter references to local cells and that works also. Steps to Reproduce: 1.Use the formula in several cells. 2. 3. Actual Results: #N/A Expected Results: The text in the file that is referred to. Reproducible: Sometimes User Profile Reset: No Additional Info: No other info.
Please attach a sample file, reduce the size as much as possible without private information, and paste the information in Menu/Help/About LibreOffice, there is a copy icon.
Created attachment 197254 [details] file referring
Created attachment 197255 [details] file that is referred to
This time it was only one value that did not work. The last line, line 38. Sometimes it seems to work and sometimes it does not. Thanks /Tomas
Reproducible Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 9517639bc3189e3ea4dc4d2f7004d4b33d754d47 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded No matter if OpenCL or Threaded are enabled or not. And if fails searching for values greater than 14801 Latest version that works on the ones I have installed. Version: 4.4.7.2 Build ID: f3153a8b245191196a4b6b9abd1d0da16eead600 Locale: es_ES
Note: Tested on Linux (various versions from 4.1 to 25.2) and when updating external links, I get error: "The following external file could not be loaded. Data linked from this file did not get updated." I can open file ElectrodevArtNr_copy.ods without problems.
Hi @raal, I think updating the links to the external file with the proper name of download files ([Ctrl+H] find & replace), should make it works, if not pls let me know to redo the samples.
The data source file needs to be added to the trusted source files in the options/security/macro security section. That may be why you cannot find the source file. /Tomas
(In reply to m_a_riosv from comment #7) > Hi @raal, I think updating the links to the external file with the proper > name of download files ([Ctrl+H] find & replace), should make it works, if > not pls let me know to redo the samples. Thank you, Miguel. After copy ElectrodevArtNr_copy.ods to ElectrodevArtNr.ods (now I have both files in the directory) I have not any error message.
This seems to have begun at the below commit in bibisect repository/OS bibisect-linux-64-6.2. Adding Cc: to Eike Rathke ; Could you possibly take a look at this one? Thanks 976208d734d6ab40d2e7e1d8b7aee8c69b1d3f08 is the first bad commit commit 976208d734d6ab40d2e7e1d8b7aee8c69b1d3f08 Author: Jenkins Build User <tdf@pollux.tdf> Date: Fri Aug 24 15:06:56 2018 +0200 source 1d0d5734ffaf6f56699c5315b6427ac832af6a13 59548: Eliminate unnecessary per element copy of matrix' first col/row | https://gerrit.libreoffice.org/c/core/+/59548
Hi all, Was this bug resolved or how do I see the status of it? Best regards, Tomas
(In reply to Tomas from comment #11) > Was this bug resolved or how do I see the status of it? You look at the Status field. It currently says NEW, so not RESOLVED.
Hi, Ok, thanks. I thought it was forgotten. Have a nice day, /Tomas
The underlying problem is that data in ElectrodevArtNr_copy.ods is in Artikelregister.A7:E102 but the lookup uses $Artikelregister.A$7:A$110 i.e. encounters 8 empty rows at the end. That worked by accident with the old code that copied to an interim vector, where empty elements were implicitly converted to empty strings and strings compare greater than numeric, whereas the non-copied elements have the original empty elements and empty compares less than anything else, which then confuses the binary search that suddenly encounters a less-than value where a greater-or-equal value is expected. To fit and fix this, specifically for LOOKUP() empty elements must compare greater than anything else as is the case when sorting a range that contains empty cells, which sorts them to the end.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b16dd0fb306944a02e5d73d4a2bbc68cf80b0b8f Resolves: tdf#163614 empty compares greater-than for LOOKUP() It will be available in 25.8.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.
Pending review https://gerrit.libreoffice.org/c/core/+/185933 for 25-2 https://gerrit.libreoffice.org/c/core/+/185934 for 24-8
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-25-2": https://git.libreoffice.org/core/commit/5b933213384796552650489a17b0af899eb03a0c Resolves: tdf#163614 empty compares greater-than for LOOKUP() It will be available in 25.2.5. 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.
Hi, Thank you so much guys! You are great! /Tomas
Hi Eike, I would like to create a test for this issue. Could you please confrim whether this issue is only reproducible when referring to another document ? I tried to move the referring sheet to the same document and it seems to work fine...
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-25-2-4": https://git.libreoffice.org/core/commit/70fb1c2b91aa500bf1ae47eba3ff113ffbfb9b7b Resolves: tdf#163614 empty compares greater-than for LOOKUP() It will be available in 25.2.4. 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.
(In reply to Xisco Faulí from comment #19) > Could you please confrim > whether this issue is only reproducible when referring to another document ? > I tried to move the referring sheet to the same document and it seems to > work fine... The bug occurred only with arrays, like it is the case when an external range reference is read from the document cache. It should also manifest if an inline array was constructed containing empty elements, but I didn't try; like in {1;2;3;;} where the trailing fourth and fifth elements are empty. (you may need a larger array or different values to trigger the binary search's failure).
(In reply to Eike Rathke from comment #21) > (In reply to Xisco Faulí from comment #19) > > Could you please confrim > > whether this issue is only reproducible when referring to another document ? > > I tried to move the referring sheet to the same document and it seems to > > work fine... > The bug occurred only with arrays, like it is the case when an external > range reference is read from the document cache. It should also manifest if > an inline array was constructed containing empty elements, but I didn't try; > like in > {1;2;3;;} > where the trailing fourth and fifth elements are empty. (you may need a > larger array or different values to trigger the binary search's failure). Hi Eike, Thanks a lot for your input. It helped a lot
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/56771bc864465d22083e6eacf8de08ab3b9e0ffb tdf#163614: sc_spreadsheet_functions_test: Add test It will be available in 25.8.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.