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.
hello, @Joel: sorry for stepping into an old bug ... would be nice if the user has any possibility, besides guessing, to check the 'adoption' of 'Aggregation.colB' as target for 'Criterion'? @Albrecht: > BTW: Why do the values change to 7, 5 and 3? maybe calc compares the 'new reference' 'Criterion-row-x' now found on 'Aggregation.colB' to the 'target' '=Bx' on Aggregation, which - oh wonder - match because being the same, and pulls the value from row-x of col 'Values' - which it finds on Raw Data - into the cell. nice side-effect: when you restrict the scope of criterion to Raw Data and! delete the content of Aggregation.B1, calc can still calculate with formulas like =SUMIF(#NAME?B$1;"=" & B3;Values ) in Aggregation.colB ... not only show former values but update them on changes in col values on Raw Data ... funny ... function wizard calculates a different result (err:509) than the sheet with autocalculate on (="Raw Data".B3), and in a way points graphical to the relevant cells e.g. B1 and B3 for C3 ... to classify such behavior as 'notabug' seems to me ... disconcerting ... tested with ver. 6.4.0.1 reg. b.
So the problem is still there and I am not the only one who fell into this trap. I think an important part of this problem is that by checking Tools > Options > LO Calc > Calculate, "Automatically find column and row labels" you allow LibreOffice to silently apply persistent changes to the semantics of your formulas. This behaviour comes quite unexpected as searching usually is thought to be non-destructive. Therefore from a users point of view this behaviour may cause many sorts of trouble as correct formulas are lost and spreadsheets will deliver wrong results. When you turn on this option you get no warning that alerts you to this problem. The help information contains no hint either. I don't know the default value of this option but I think this feature should be off by default and should issue a warning if you turn it on. Maybe changing the implementation of this option such that is actually does a "find" only and not an unexpected "find & replace" would be a better solution.
Set bug to Unconfirmed as the last status before closed as Notabug was also Unconfirmed. Reopened is only allowed for developers.
behaviour confirmed, setting new, details: Criterion - without quotes - is changed to 'Criterion' - with quotes - on change of the scope from global to sheet, but not changed back with undo, if you change back the scope to global and then remove the quotes the calculation will work as before ... with: Version: 6.1.6.3 (x64) Build ID: 5896ab1714085361c45cf540f76f60673dd96a72 CPU threads: 8; OS: Windows 6.1; UI render: GL; Locale: de-DE (de_DE); Calc: CL
issue 1. the change from criterion - without quotes - referencing the global name to 'criterion' - with quotes - referencing the local 'column header'? isn't undone by undo, issue 2. shouldn't the change of the scope from 'global' to 'raw data' replace the referencing formulae with '=SUMIF('Raw Data'.Criterion;"=" & B3;Values )' instead of initiating a new 'name search'? imho in other similar situations formulae are adapted to 'not to break things',
As explained earlier here (comment 1), this is due to "automatic labels" ('Criterion' with single quotes hints to that) that come with all sort of traps and surprises, we should deprecate them. Use defined labels instead (which wouldn't help here in case of a name conflict though). Labels get precedence over named expressions of the same name. Automatic labels are a mess and the worst invention Excel came up with and they deprecated them as well years ago. Won't fix, closing.