Apache OpenOffice (AOO) Bugzilla – Issue 98185
Spreadsheet sheet copy changes sheet reference in formulas
Last modified: 2016-11-15 20:35:09 UTC
If you a spreadsheet with say three sheets: Sheet1, Sheet2, Sheet3. On Sheet2 you reference a cell on Sheet1 eg =Sheet1.$A$2 and then make a copy of Sheet2 after Sheet3 eg Sheet2_2 it will update the reference on Sheet2_2 to say =Sheet2.$A$2 instead of staying as =Sheet1.$A$2. You can use =$Sheet1.$A$2 to work around this issue but Excel doesn't require this so it would be good to use similar conventions as they do.
on Sheet2 you reference a cell from Sheet1: =Sheet1.$A$2 Sheet1's index is 0, Sheet2's index is 1 after copying Sheet2 after Sheet3, the new newly created sheet will reference to =Sheet3.$A$2, the next left sheet ... > You can use =$Sheet1.$A$2 as you mentionend, this will do the trick :-) a sheet name without leading $ is treated as a relative address Oliver
Created attachment 85857 [details] Testcase showing clearly what's going wrong First sheet describes exactly the expexted formulas and the ones generated automaticaly by sheet copy