Description: 3 sheets in the document : 'Elements' 'UPN' 'Feuille13' Formula in Sheet 'Elements' : = RECHERCHEV(R86;UPN;8) If I delete the sheet 'Feuille13' (but keeping the sheet 'UPN') then the formula here above becomes : =RECHERCHEV(R86;#NOM ?;8) While the sheet 'UPN' still exists Steps to Reproduce: 0. 3 sheets in a file : A, B, C 1.in sheet A create formula with link to sheet B 2.delete sheet C 3.but in formula with link from A to B Actual Results: formula n sheet A is transformed within a lost link to an inexisting sheet, which is wrong since the sheet still exist Expected Results: the link should not be impacted by deletion of sheet C Reproducible: Always User Profile Reset: No Additional Info: Version: 7.5.3.2 (X86_64) / LibreOffice Community Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3 CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: fr-FR (fr_FR); UI: fr-FR Calc: threaded
Created attachment 188223 [details] Example of the bug in my sheet
I can't create a formula of the type =VLOOKUP(2,UPN,1) in the first place, I straight away get a #NAME? error (and the formula is changed to =VLOOKUP(2,upn,1) even though I do have a UPN sheet). Could you please attach an example document, in its state before the sheet is deleted, so we can test in the same conditions? Was the file created in LibreOffice or another tool? Thank you. Tested with: Version: 7.6.0.0.beta1 (X86_64) / LibreOffice Community Build ID: be55b15d98c5f059483845a183fcb5ea8023d27c CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded
Created attachment 188230 [details] ods file where I get the bug Hi Stéphane, Here the ods file where I got what seems to be a bug. 1) look the formula in the cell U232 in sheet 'Eléments STR'. There isn't any link to the sheet 'feuille13' N.B. data zones are defined in this file. 'UPN' si, for example, a data zone in the sheet 'UPN' 2) delete sheet 'feuille13' 3) there a bug into the formula : the link to the sheet 'UPN' is broken Thanks for your help Eric
Confirmed in: Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: d74344f6cae0cf1c12f08249c8f49be1374fb98f CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded If other sheet deleted, no bug. If Sheet Feuille13 is moved to before UPN, and then deleted, no bug. Bibisected with linux-64-7.2 repo to first bad commit c7baa2dd3209b9db60ee0699536135109918915d which points to core commit 957d1e977d25385151c99e401124ad58217e53dd which is a cherrypick of: commit 0c0444c44107f1a18f23dd0833d462d8dbf56569 author Tünde Tóth <toth.tunde@nisz.hu> Wed Jul 21 16:04:37 2021 +0200 committer Eike Rathke <erack@redhat.com> Fri Jul 30 17:57:10 2021 +0200 tdf#126926 sc DBData: delete the database range if some part of the reference became invalid. Reviewed-on: https://gerrit.libreoffice.org/c/core/+/119354 Not reproduced in 7.1, but once Large Spreadsheets is turned on, I get crashes, so maybe related to a potential UPN column name? Crash reports, for the record: - 7.1: https://crashreport.libreoffice.org/stats/crash_details/c091cdc8-841d-4518-b25d-c78a2dce096e - 7.2: https://crashreport.libreoffice.org/stats/crash_details/ac78a34f-b721-48eb-840a-62a69c227f25 - 7.3: https://crashreport.libreoffice.org/stats/crash_details/9a54f65a-64e7-4a8e-bb16-4494ee19de04 - No crash since 7.4. Tünde, can you please have a look? Eike, what are your thought on what looks to me like an "off-label" use of a sheet name in the second argument of a VLOOKUP()?
Tünde Tóth committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/94ca402cd1fe2fd9776d08448f7216b7f638e69a tdf#156174 sc DBData: fix regression of database ranges It will be available in 24.2.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.
Fix verified in: Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 24d0a62bd75b9a895c419aa165da648ab18f134d CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Thank you Tünde! Could we get it into 7.6 too?
Tünde Tóth committed a patch related to this issue. It has been pushed to "libreoffice-7-6": https://git.libreoffice.org/core/commit/04098a535bf25494379678b9539c4987cb33431f tdf#156174 sc DBData: fix regression of database ranges It will be available in 7.6.1. 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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/f35b540279b00575ff79eda1c65fb0f8caad86bf Resolves: tdf#158223 Revert "fix" for tdf#156174 and follow-up It will be available in 24.2.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-6": https://git.libreoffice.org/core/commit/c0b46a8270507dc59b731e9c996960374b0db472 Resolves: tdf#158223 Revert "fix" for tdf#156174 and follow-up It will be available in 7.6.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.