Created attachment 178795 [details]
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.
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.
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.
Version: 126.96.36.199.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
and also on current master
Also in Version: 188.8.131.52.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: 184.108.40.206.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