Created attachment 178753 [details] refTest.xlsx Steps to Reproduce: 1. Open the attached xlsx file. 2. Ctrl+A to select all, then right-click on any column header and select "Show Columns". Observe that the cell formulas in the last two columns do not reference to any cells in columns N:S. 3. Delete columns N:S. Current Result: Cells in the last two columns (e.g. W:X) are shown as #VALUE!. Note that although cells W8:X8 are not #VALUE!, the cell reference is already broken and a hard-recalculation will make it #VALUE!. Expected Result: Cells in the last two columns should remain is they were before the (previous) columns N:S were deleted, as the formula references in these cells did not referenced to any cells in those deleted columns. Version: 7.4.0.0.alpha0+ / LibreOffice Community Build ID: 279abd670317760dd1d58c549a0589c3a31c1e4d CPU threads: 8; OS: Linux 5.16; UI render: default; VCL: gtk3 Locale: zh-CN (zh_CN.UTF-8); UI: zh-CN Build Platform: Fedora34@X64, Branch:master, bibisect-linux-64-7.4-CN Calc: threaded Fedora 34, Gnome 40.8 x11.
Also in: Version: 7.2.5.2 / LibreOffice Community Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5 CPU threads: 8; OS: Linux 5.16; UI render: default; VCL: gtk3 Locale: zh-CN (zh_CN.UTF-8); UI: zh-CN Calc: threaded I noted that, when resave the refTest.xlsx as refTest-new.xlsx, quit libreoffice and then reopen the refTest-new.xlsx, then follow the above steps, the formula reference will not broken.
The refTest.xlsx was generated by MSO (2019?).
Repro Version: 7.4.0.0.alpha0+ / LibreOffice Community Build ID: 53560aa06462f1cf04d69acafed20da971bc5c27 CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3 Locale: cs-CZ (cs_CZ.UTF-8); UI: en-US Calc: threaded Jumbo works in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
Repro in version Version: 6.0.0.0.alpha1+ Build ID: 6eeac3539ea4cac32d126c5e24141f262eb5a4d9 CPU threads: 3; OS: Linux 5.10; UI render: default; VCL: gtk3; Locale: en-IN (en_IN); Calc: group threaded
Simpler STR, but with different specific error codes. STR: 1. Open attachment 178753 [details] from comment 0. 2. Optional: [CTRL]+[END] 3. Optional: [SHIFT]+[F9] (3 times) (i.e. "tools > detective > trace precedents" (3 times)) 4. Optional: take note of cell AC12: =(0.34*AA12/1000+0.078)*J12 5. Select column AB. 6. Delete column AB (shortcut [CTRL]+[-] after step 5). 7. Take note of the content of new cell AB12 (former cell AC12 in step 4): =(0.34*#REF!12/1000+0.078)*L12 Notes: * Some older versions of LO might not support some of the aforementioned shortcuts or the "trace precedents" feature. These are non essential in order to reproduce the problem. * Depending on LO version, the error code might be either #REF! or #VALUE!. * This is repro already in at least LO 4.3 and up to current alpha. * When trying to reproduce this in LO 4.2, I got crash (possibly unrelated to this report). * This is _not_ repro in LO 4.0.
It is impossible to bibisect this. Both Linux (4.2 and 4.3) and Windows (4.3) show a problem, where LibreOffice hangs when you try to delete columns N:S. I even determined the exact commit in 4.2 where this hanging started and the previous commit does NOT show the reference breakage bug.
(In reply to Buovjaga from comment #6) > hangs when you try to delete columns N:S. That matches my experience in comment 5 with LO 4.2, with the difference that the specific column to be deleted is AB only, instead of N:S as in comment 0. Unfortunately, this is still repro nowadays. There should be no #REF! nor #VALUE! error, especially when the "precedent" cells are not modified in any way (no change in their address). The address that indeed changes is the column of the cell that contains the formula that fails: column AC into column AB in STR from comment 5.
Created attachment 187215 [details] Excel after column delete
Created attachment 187216 [details] Calc after column delete
I wanted to rule out that there is a problem with the spreadsheet. 1) The file if saved to ODS shows the same #VALUE error when recreating the steps. 2) I opened the spreadsheet submitted with Excel 365. Excel deletes the columns and the formula calculates and shows the value correctly. Please note the formula in the cell W12 in the Excel screenshot and compare it to the formula in the Calc screenshot. The actual formula is correct in Calc even if it shows #VALUE. It would appear to me to be calculation engine issue. I was able to check back to 6.4.0alpha. This version has the issue also.
(In reply to bunkem from comment #10) > I wanted to rule out that there is a problem with the spreadsheet. > > 1) The file if saved to ODS shows the same #VALUE error when recreating the > steps. Not if you close the new file first, whether ODS or XLSX. > > 2) I opened the spreadsheet submitted with Excel 365. Excel deletes the > columns and the formula calculates and shows the value correctly. > > Please note the formula in the cell W12 in the Excel screenshot and compare > it to the formula in the Calc screenshot. The actual formula is correct in > Calc even if it shows #VALUE. Not when following the steps posted in comment 5. Those steps show that the "Precedent" cells don't even have to move in order to trigger the problem in column AC (that's the original column with the _first_ error, before deleting other column(s)). So, my experiences with attachment 178753 [details] from comment 0 (using STR from comment 5) have been slightly different than bunkem's. 1. Open the file with a recent version of LO (in my case, I tested with 7.6.alpha built this week). 2. Without changes, save the file with a different name. 3. Close the file. This is key! 4. Open the new file. Delete column AB. > No problem in any formula nor in the calculated results. * The original XLSX file is 12KB whereas the new XLSX file is 9KB in size. * The result is independent of the file format (XLSX or ODS); the key is to close the new file after saving with a new name. > > It would appear to me to be calculation engine issue. * Re-saving, closing and opening the new file works correctly in the same version of LO Calc. This is unnecessary with other spreadsheet tools. I guess someone with enough knowledge of the XLSX format could check the differences between the original file and a re-saved copy in order to find out what's the difference that triggers the error, so it can be solved without having to re-save and reopen the original XLSX file.
Thanks @ady! I didn't think to "Save as", close and try again. Yes, I can confirm that when you "Save as", close and reopen, the column delete works as expected. Used: Version: 7.5.3.2 (X86_64) / LibreOffice Community Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3 CPU threads: 8; OS: Mac OS X 12.6.5; UI render: default; VCL: osx Locale: en-CA (en_CA.UTF-8); UI: en-US Calc: threaded