Bug 147916

Summary: When copying a cell to a newly inserted sheet, absolute sheet name reference is not reserved thus the pasted formula is broken
Product: LibreOffice Reporter: Kevin Suo <suokunlong>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: NEW ---    
Severity: minor CC: aron.budea, ilmari.lauhakangas, miguelangelrv
Priority: medium    
Version: 5.2.0.4 release   
Hardware: All   
OS: Linux (All)   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=147879
Whiteboard:
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 108917    
Attachments: sheet_name_ref_test.ods

Description Kevin Suo 2022-03-11 02:47:36 UTC 
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
Comment 1 Kevin Suo 2022-03-11 02:50:18 UTC 
It is noted that when copy pasting to an existing sheet (e.g. sheet2 or sheet3), the pasted result is correct.
Comment 2 m_a_riosv 2022-03-11 15:51:13 UTC 
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.
Comment 3 Buovjaga 2022-12-20 11:18:03 UTC 
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