Created attachment 178795 [details] sheet_name_ref_test.ods Steps to Reproduce: 1. Open the attached ods file. 2. Select A1:B2 in sheet1, ctrl+c to copy. 3. Insert a new sheet between sheet1 and sheet2 (it's named as "Sheet 5"). 4. Select a cell in "Sheet 5", ctrl+v to paste. Current Result: In cell B2 of "Sheet 5", the pasted formula is =VLOOKUP(A2,$sheet3.A:B,2,0) thus the result is #N/A. It seems that the absolute reference $ is ignored during copy paste of the formula. Expected Result: In cell B2 of "Sheet 5", the pasted formula should be =VLOOKUP(A2,$sheet4.A:B,2,0) thus the result should be 100, because the source formula uses absolute reference and it should be retained during copy paste. Reproducible on Version: 7.3.2.0.0+ / LibreOffice Community Build ID: 789fbf732ad6db002fd83b307385940c392cc457 CPU threads: 8; OS: Linux 5.16; UI render: default; VCL: gtk3 Locale: zh-CN (zh_CN.UTF-8); UI: zh-CN Calc: threaded and also on current master Also in Version: 6.1.7.0.0+ Build ID: 5b3765f4d881e7ddefd0c4aad6886a46f000b4fc CPU threads: 8; OS: Linux 5.16; UI render: default; VCL: gtk3; Locale: zh-CN (zh_CN.UTF-8); Calc: group threaded
It is noted that when copy pasting to an existing sheet (e.g. sheet2 or sheet3), the pasted result is correct.
If you first insert the new sheet and then copy and paste it works fine. But if you first copy, then in paste it retains the position of the reference sheet when copy it's done, reference to sheet3. Maybe at copying the sheet number it's used instead of the sheet name.
Repro already in 5.2. Arch Linux 64-bit, X11 Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: f6b4c3fb68f9b36e1e64fc4a4c680ab1bb7fb013 CPU threads: 8; OS: Linux 6.0; UI render: default; VCL: kf5 (cairo+xcb) Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded Jumbo Built on 20 December 2022