GNOME Bugzilla – Bug 409997
Names ranges can have names that are valid as cell references
Last modified: 2010-06-07 06:56:20 UTC
By changing a worksheet between A1- and R1C1-style references, it is possible to have an expression that is valid both as a cell reference and as a named range. You can't setup ambiguous names directly, but you can with a little effort -- for example: - start with Sheet1 using A1-style references - define a named range called 'R1C1' referring to range B1 - change Sheet1 to use R1C1-style reference - what does R1C1 now refer to? The code will look up a value from a cell reference before a named range (i.e. '=R1C1' will give you the value from R1C1-the-cell rather than R1C1-the-range), but having the ambiguous expression means that values can change if you swap a sheet between A1- and R1C1-style references. Excel tries to avoid this problem by making you redefine ambiguous names when you change reference style. But: - This is a *really* ugly solution. - As reference-style is a worksheet-level property in Gnumeric (not application-level like in Excel), this won't avoid problems with ambiguous names if expressions are referring to names on worksheets using a different reference-style. - You can have expressions referring to closed workbooks, so redefining names isn't forced, giving you the same problem in Excel. The cleanest way to avoid this would be simply to disallow any names that can be interpreted as a cell reference, in either A1 or R1C1 style, at all times. However, this would break compatibility (to a very small extent) with valid Excel workbooks or existing Gnumeric files which have these potentially ambiguous names. Would automatic renaming of invalid names on opening these spreadsheets be needed/possible?
Created attachment 82980 [details] Test case showing ambiguous names I was partly wrong in my previous comment; changing a worksheet's reference-style does not change the values displayed even if there are ambiguous names. This is because Gnumeric can tell the difference between name- and cell-versions of a name. The spreadsheet I'm including shows this in action; look at cells R1C3 and R2C3 on Sheet1. These both contain the formula '=R1C1' but -- as one is for R1C1-the-name and the other is for R1C1-the-cell -- they have different values. That's slightly confusing.
Gnumeric resolves these to either a cell reference or a name reference at the time the expression is parsed. This means that the meaning does not change just because you switch back and forth between R1C1 mode. But the meaning might change if something makes a string out of the expression and re-parses it. That could happen if you edit a cell but don't actually change it or just change some other part of it. So clearly it is not a good situation to be in.
In fact in R1C1 mode we still allow names that look like R1C1 and prohibit names to look like A1.
ANd to make it more interesting: new gnumeric, change sheet size to 128 columns. We can now define a name "DZ1" to mean "help" change the size of the the sheet to 256 columns. now DZ1 does not evaluate to "help" anymore.
I think we should not worry about the import of risky names (since what can we do about that anyways) but at least we should make sure that one can't define them anymore inside gnumeric.
This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.