Created attachment 102704 [details]
Example file to show the effect.
How to reproduce:
The attached spreadsheet contains a tab “Aggregation”. Cells C3 … C5 refer to the global name “Criterion”. These cells should display the values 16, 19 and 5. Press CTRL+F3 to show the “manage names” dialog. Change the scope of the name “Criterion” from “Document (Global)” to “Description of the Problem”. Note that the values change to 7, 5 and 3. Now changing the scope of “Criterion” back to “Document (Global)” or to “Aggregation” does not change these values. The wrong values persist saving and reloading the document. The reason for this behaviour seems to be that Calc silently changes the formulas referring to the range “Criterion”: This name is set between quotes. I have got the impression that “Criterion” is an undocumented reserved name as the behaviour is different if you use a name different from “Criterion”.
Expected behaviour: The cells should display an error as the name “Criterion” is not defined for the “Aggregation” sheet. Changing the scope to “Document (Global)” or “Aggregation” should restore the original values. Changing the scope should not trigger changes in the formulas. If the special behaviour is intended then a warning should appear explaining the special meaning of the name “Criterion”.
Note: Changing the scope of the name “Values” seems to work as expected.
This is cause by the cell B1. This cell contains the word "Criterion" and in Tools > Options > LO Calc > Calculate, "Automatically find column and row labels" is checked.
When a name is defined with the scpoe "Sheet1", this name can be used only in "Sheet1".
So changing the scope to a different sheet have the same effect as deleting this name.
This name deleted, Calc search for a Label with the same name and find Criterion in B1. The name in the formula is replaced by the Label 'Criterion' (quote surrounding).
And is not reverted when creating again the name.
Deleting the word "Criterion" from B1 or (better) uncheck "find labels" in options and the problem is gone.
Waiting for another Calc expert opinion to close this bug.
Thank you for clarifying the reason of this behaviour, and in some technical sense Calc may work correctly.
Nevertheless I think that this behaviour is a serious bug.
Calc has hundreds of settings, and an average user does not know which of these settings are relevant for an operation like changing the scope of a name. Unfortunately Calc applies - by default! - other permanent changes than just doing the requested change of scope.
So I think the bug is in the user interface: Calc should notify the user if it does any other changes than those directly requested. The notification should explain the reasons and the consequences of the intended additional changes. Silent changes to the content may have disastrous effects if they go unnoticed.
BTW: Why do the values change to 7, 5 and 3? In my opinion comparing the string 'Criterion' to the numbers contained in the cells makes no sense and therefore Calc should report an error.
I spoke with one of our most experienced developers (Markus) and he agreed with comment 1. Closing as NOTABUG.
Please do not reopen the bug as the issue has been explained fully and now we have one of our most knowledgeable calc developers saying this is not a bug.
Thanks for your understanding. Feel free to email the dev list if you want a further explanation.