Created attachment 112992 [details] Simple demonstration of bug per Description When a label is created, the reference is of form $sheet.$col$row. The sheet portion of the reference is an absolute reference. The form sheet.$col$row is not supported (i.e. with the sheet portion of the reference being a relative reference). Therefore if the sheet is copied (i.e. Edit > Sheet > Move/Copy), the labels on the new sheet always reference cells on the original sheet rather than on the new sheet itself. This can be demonstrated as follows. In the attached file, LabelRelSheetAddress.ods, do "Insert > Names > Labels >". In the dialog field for specifying the cell of the label, the reference is $Sheet1.$A$1 (wherein the sheet reference is an absolute reference). It doesn't allow changing it to Sheet1.$A$1 (wherein the sheet reference would be a relative reference). This is a problem when copying the sheet as follows. 1. "Edit > Sheet > Move/Copy (Copy is preselected) > -move to end position- > OK" 2. On new sheet "Sheet1_2", the reference in the formula of cell B2 to 'label1' is to cell Sheet1.$A$2, so the sheet reference in 'label1' was indeed treated as an absolute reference. There's an additional problem that can be demonstrated with the attached file LabelRelSheetAddress.ods (before the demonstration steps above are done) as follows. 1. "Edit > Sheet > Move/Copy (Copy is preselected) > Insert before Sheet1 > OK" 2. On the new sheet "Sheet1_2", the cell B2 is evaluated to "#NAME?". The reference to 'label1' seems to be the problem even though "Insert > Names > Labels" shows that 'label1' is defined as column label $Sheet1.$A$1. 3. "Edit > Sheet > Move/Copy (Move) > -move to end position- > OK". Now on "Sheet1_2", cell B2 is evaluated to 303. Moving the sheet shouldn't change the evaluation of references to labels. See related Bug 88963.
Hi @dwyoung, thanks for reporting. ... When a label is created, the reference is of form $sheet.$col$row. ... Changing the scope from Document(Global) to Sheet, lets a non absolute sheet. What I think is right. So IMO not a bug. Please if you are not agree reopen it. The rule is one bug one report. So please report the second issue with a new report. I have verified the problem, so I'll set up it as new.
Created attachment 113029 [details] Screenshot of dialog related to the bug as described.
Comment on attachment 113029 [details] Screenshot of dialog related to the bug as described. Attachment LabelRelSheetAddress.jpg is screenshots of dialogs. On the left is the dialog for defining a label. I'm not finding a control in that dialog for setting scope, and that seems to be the essence of the problem. On the right is the dialog for defining a name and it has control for setting scope.
Please see Comment 3 regarding reopening of this bug.
Forgive me @dwyoung, you are right I was wrong with named ranges dialog. I'll try later.
Win7x64 Version: 4.4.0.3 Build ID: de093506bcdc5fafd9023ee680b8c60e3e0645d7 1) Reproducible no option for change sheet from absolute to relative. But I'm not sure this is a bug, the label ranges I think are not part of sheet so they are not copied with it, like named ranges. In the help: "You can set label ranges that contain the same labels on different sheets. LibreOffice first searches the label ranges of the current sheet and, following a failed search, the ranges of other sheets." 2) Reproducible, saving and reopening solves the issue. 3) I think not an issue here, seems recover the point 2), but always remains calculated over Sheet1. Please review.
m.a.riosv, thank you for investigating. 1. Whether it's a bug or missing functionality, it would be good if labels could be referenced as relative references. Labels for identifying rows and columns (and individual cells as well), are important for building spreadsheets that are structured in a manner that makes them more understandable and maintainable. Calc supports copying sheets. When a sheet is designed for analysis of a single entity (e.g. economic output of a nation), the obvious way to analyze multiple instances of the same type of entity (i.e. to analyze a number of nations) is to copy the spreadsheet for one instance of the entity and then change the data for that second instance, but doing the same analysis of the data for each instance of the entity. In such a case, the formulas in the second instance that use column and row headings would likely desire for the labels they reference to refer to the column and row headings on their own sheet. So when the sheet copy is done, it would be good if the labels on the first sheet to be treated as relative references. Labels are better for this than names because a formula using a label is more understandable than a formula using a name since a labels that are column or row headings are conspicuous in their presence. On the other hand, the existence of names is determined by doing "Insert > Names > Manage", which is inconvenient. 2. I'm sorry I'm not understanding your comment. It seems like your comment says that it's acceptable for the state of a sheet to be changed by the process of saving and then opening the file - and I would be surprised for someone to say that. Can you explain more explicitly? 3. It's a bug for the state of the file to be changed by doing a file save followed by a file open. Also, it's clear to me that absolute and relative references should be supported for labels the same as they are for names. Thank you for your consideration.
1) I think perhaps it's not so easy to implement, but who knows, report a new bug and set the status as enhancement. 2) Sorry if I have not explain well, only that saving and reopening helps but of course the bug is there. Changed status to new for: " 2. On the new sheet "Sheet1_2", the cell B2 is evaluated to "#NAME?". The reference to 'label1' seems to be the problem even though "Insert > Names > Labels" shows that 'label1' is defined as column label $Sheet1.$A$1. "
The first part of this is an enhancement - although as already mentioned probably not a very likely one to be dealt with. The second part was dealt with on bug 89019. If you enable: Tools - Options - LibreOffice Calc - Calculate - General Calculations - Automatically find column and row labels the label reference will work after copying the sheet. So for what's left, setting: Severity -> enhancement
Labels with relative sheet references don't make much sense. As soon as rows or columns are deleted in a sheet where an affected label range is used in a formula on another sheet that formula is out of sync. Similar for insertions. What might be possible instead could be to copy labels when a sheet is copied, along with adapting all formulas on the copied sheet that use such a copied label range. Btw, "Automatically find column and row labels" should be used with care and generally is not advisable.