When copying a sheet and inserting the copy before the first sheet (tab) breaks the links between cells in different sheets. Encountered on: LibreOffice 3.5.7.2 Build ID 350m1 (Build:2) running on Ubuntu Linux 12.04.5 LTS, kernel 3.2.0-119-generic-pae #162-Ubuntu SMP Tue Dec 6 14:37:52 UTC 2016 i686 i686 i386 GNU/Linux. How to reproduce: Set up a new spreadsheet as follows: 1. Create new spreadsheet 2. Click "Sheet3" tab 3. Enter a value in cell A1 (e.g. "123") 4. Click "Sheet1" tab 5. In cell A1, enter "=", then click "Sheet3" tab, click cell A1, hit ENTER. 6. Click "Sheet1" tab, click cell A1. Value in this cell will now be: "=Sheet3.A1". Demonstrate correct behavior as follows: 7. Right-click "Sheet1" tab, select "Move/Copy Sheet", under "Action" select "Copy"; under "Location" select "Insert Before Sheet2", click "OK". This will create a new tab "Sheet 1_2" after the "Sheet1" tab. 8 Click "Sheet1_2", click cell A1. Value in this cell will now be: "=Sheet3.A1". This is correct behavior. Demonstrate incorrect behavior (i.e. expose the bug) as follows: 9. Right-click sheet1_2, select "Delete Sheet", click "Yes" 10. Right-click "Sheet1" tab, select "Move/Copy Sheet", under "Action" select "Copy"; under "Location" select "Insert Before Sheet1", click "OK". This will create a new tab "Sheet 1_2" before the "Sheet1" tab. 8 Click "Sheet1_2", click cell A1. Value in this cell will now be: "=Sheet2.A1". THIS IS INCORRECT BEHAVIOR. The value should be "=Sheet3.A1" as was the case when the copy was inserted after the "Sheet1" tab.
Could you please check Bug 86652, is this related or a duplicate? Looks like the same issue with relative reference.
(In reply to Frank van Wensveen from comment #0) > .... > Demonstrate incorrect behavior (i.e. expose the bug) as follows: > 9. Right-click sheet1_2, select "Delete Sheet", click "Yes" > 10. Right-click "Sheet1" tab, select "Move/Copy Sheet", under "Action" > select "Copy"; under "Location" select "Insert Before Sheet1", click "OK". > This will create a new tab "Sheet 1_2" before the "Sheet1" tab. > 8 Click "Sheet1_2", click cell A1. Value in this cell will now be: > "=Sheet2.A1". THIS IS INCORRECT BEHAVIOR. The value should be "=Sheet3.A1" > as was the case when the copy was inserted after the "Sheet1" tab. If you want it behaves in that way use absolute references for the sheet in the formula: =$Sheet3.A1 It's the same behaviour like if you copy the formula from one sheet to another.
BTW if you like such behavior you can push [Ctrl] while dragging the sheet tab.
If this is the same behavior as when copying a formula from one cell to another, why is there a difference in behavior between copies inserted before or after the first tab? This seems inconsistent.
For me the a bug is copying before previous sheets, relative reference fails by one sheet. Add a new sheet4 at end, Copying from: Sheet1 before Sheet2, formula should change to Sheet4.A1 but has Sheet3.A1 Sheet1 before Sheet3, formula should change to Sheet5.A1 but has Sheet4.A1 Please if you are agree, change the title accordingly.
*** This bug has been marked as a duplicate of bug 85538 ***