Description: When I duplicate a sheet and then later delete original sheet ,in the new sheet data validation loss reference to cells. For example this formula "E(F22 >=F7;F22 <= F9)" change in "E(#RIF! >=#RIF!;#RIF! <= #RIF!)" To highlight the problem it is necessary to save the file and then reopen it after the original sheet has been deleted Steps to Reproduce: 0) Insert "validation data" formula to check value of an other cell 1.duplicate sheet 2.delete original sheet 3.save file 4. reopen file Actual Results: Into the validation data the formula contains "E(#RIF! >=#RIF!;#RIF! <= #RIF!)" Expected Results: "E(F22 >=F7;F22 <= F9)" Reproducible: Always User Profile Reset: No Additional Info: Version: 7.5.3.2 (X86_64) / LibreOffice Community Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3 CPU threads: 12; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win Locale: it-IT (it_IT); UI: it-IT Calc: CL threaded
Please attach a _minimal_ file for others to follow the steps. Use the "Add an attachment" link in the bug report page. Beware that the file will be publicly available. https://wiki.documentfoundation.org/QA/Bugzilla/Sanitizing_Files_Before_Submission
Created attachment 188341 [details] File to test
Function name is AND in English, E in Italian. Works for me: Version: 7.4.7.2 (x64) / LibreOffice Community Build ID: 723314e595e8007d3cf785c16538505a1c878ca5 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: default; VCL: win Locale: es-MX (es_ES); UI: en-US Calc: CL Must test on version 7.5.
(In reply to LeroyG from comment #2) > Created attachment 188341 [details] > File to test Thank you but no, it is not a file to test. In your attachment 188341 [details], cell F22 has a Validity (Custom) of: AND(#REF! >=#REF!;#REF! <= #REF!) ...which doesn't help in testing the steps posted in comment 0. We need a clear, minimal example with clear steps for users to perform on such minimal clear file; not a random file that would require investigation. Time is not to be wasted.
(In reply to ady from comment #4) > Thank you but no, it is not a file to test. > > In your attachment 188341 [details], cell F22 has a Validity (Custom) of: > > AND(#REF! >=#REF!;#REF! <= #REF!) > > ...which doesn't help in testing the steps posted in comment 0. Maybe this is the confirmation of the bug. When I saved the file there were actual references not #REF!. I would retest.
Created attachment 188357 [details] File to test, without messing with duplicate sheet.
Created attachment 188358 [details] screenshot I can confirm the bug. My first attachment was the Sheet1_3, the second copy of the original Sheet1, renamed to Sheet1. Now I followed the steps to reproduce (with my second attachment), and the validity formula changes to AND(#REF! >=#REF!;#REF!<=#REF!).
The prior attachments are less than ideal but usable (if the user/tester knows what to look for). Further investigations confirm the bug: Using any cell reference in any of the possible validation types (not only custom but any and all of them), will result in a #REF! error in the source values of the Validity dialog when following these steps (which use just one of the possible validity types, as example) 1. A1: 1 2. A2: 3 3. A3: Menu Data > Validity: 3.1. Allow: Whole Numbers 3.2. Data: valid range 3.3. Min: =A1 (Calc will delete the "=" sign when closing the dialogue) 3.4. Max: =A2 (Calc will delete the "=" sign when closing the dialogue) 3.5. OK 4. A3: 2 5. Duplicate the current worksheet (aka "copy" worksheet in older versions). 6. Delete the original worksheet. <- The corruption is generated at this point!!! 6.1. A3: 3 (the value is rejected, same as trying the prior value "2" again). 7. Save the file. 8. Close the file; reopen. <- This is required in order to _see_ the problem. 9. A3: 3 (the value is rejected, same as trying the prior value "2" again). 10. A3: Menu Data > Validity; the Min and Max fields will show the #REF! error. Notes: * Step 9 is optional. * Step 6 already causes the corruption/problem. * Step 6.1 is optional. * Adding a "6.2" step to show the Min and Max fields will not show the #REF! error yet. Closing the dialogue with OK at this point "restores" the correct values. The problem is reproduced in LO 4.2 but not in 4.1 and older > regression.
Created attachment 188366 [details] Simplified test and STR I'm attaching a simple ods file for testing with the STR from comment 8. This test points to the problem in the Validity dialogue. It also demonstrates that: * there is no relation to the specific Calc function (e.g. AND()); * there is no relation to circular references of any form; * there is no direct relation to the "custom" form of Validity, since the problem can be reproduced with other types of Validity.
step 5. Duplicate the current worksheet (aka "copy" worksheet in older versions). -> copy the sheet after current one ( to the last position), suprisingly this is important. This seems to have begun at the below commit in bibisect repository/OS bibisect-42max. Adding Cc: to Kohei Yoshida ; Could you possibly take a look at this one? Thanks 0b21f7cae90b8405aa4bc081e0024f05b5438c1c is the first bad commit commit 0b21f7cae90b8405aa4bc081e0024f05b5438c1c Author: Matthew Francis <mjay.francis@gmail.com> Date: Sat Sep 5 19:29:39 2015 +0800 source-hash-8eb6de29009d009b8fa61fa8bb0e3bc60c7bfdf9 commit 8eb6de29009d009b8fa61fa8bb0e3bc60c7bfdf9 Author: Kohei Yoshida <kohei.yoshida@gmail.com> AuthorDate: Thu Jul 25 22:35:47 2013 -0400 Commit: Kohei Yoshida <kohei.yoshida@gmail.com> CommitDate: Tue Jul 30 23:49:55 2013 -0400 Make this guy unused, and remove it. Change-Id: Ib4a2f195b3ff1d291b0ffe99c27b1d3e807252d5