[Solved] Naming cells doesn't work in 3.0

[opuolakk]

Hi!

I made a spreadsheet document with OOo 2.3 and parts of it stopped working when I updated to 3.0. I had named a range of cells (not adjacent but completely separate and randomly positioned) "menot2008" using Insert>Names>Define and filling the cell references "$Sheet1.$AE$13;$Sheet1.$AH$34;$Sheet1.$AQ$56" for example in the 'Assigned to' -field. Then i wrote the formula "=AVERAGE(menot2008)" to a cell and got the average of the "menot2008"-cells.

But after I updated, the cell says only "#NAME?". When i try to redo the cell naming process, the 'Define Names' -window won't close but returns an error message "Invalid expression" obviously referring to the list of cells separated by semicolons that I have filled in the 'Assigned to' -field. Naming single cells or square ranges of cells using a colon still works just fine. What could be the reason for this? Please someone help me!

Thank you!

[Villeroy]

Just tried a name foo ($Sheet1.$A$1:$A$2;$Sheet1.$C$1:$C$2) (including the braces). =AVERAGE(Foo) gives the average of both ranges.

[opuolakk]

Hooray! It works! Clearly the braces have to be added in 3.0. Thank you very much!

[acknak]

OTOH, if you select those same cells, then type a name in the range box and press Enter, you get an alert that says: "The selection needs to be rectangular in order to name it." Likewise, it is impossible to enter a list of ranges in the Insert > Names > Define dialog, except by typing it manually. The same restriction exists for 2.4.2 and 3.0.

I don't know what the standard rule is; I can't find any place that specifically says that Excel allows named non-rectangular ranges, and I can't find any specific requirements for named ranges in either the online help or the ODF Formula spec. I don't see why it should not be allowed, but it may be that the manually-entered range list should be rejected but isn't checked.

I can confirm that a sheet created under 2.4.2 opens with an error under 3.0, which (to my thinking) should be reported as a bug. I expect that would at least get an explanation, if it turns out that this is the intended behavior. If nothing else, it should be documented clearly.

Adding the parens does allow it to work in 3.0, but the same syntax does not work in 2.4.

[Villeroy]

Filed issue http://qa.openoffice.org/issues/show_bug.cgi?id=97728

[acknak]

I see in the comments on the issue a reminder that OOo 3 introduced the new reference operator "~" that should be used for constructing lists of references. This is actually mentioned in the online help, under "reference operators":

Reference concatenation using a tilde character is a new operator, available starting with OpenOffice.org 3.0 and StarOffice 9. When a formula with the tilde operator exists in a document that is opened in previous versions of the software, an error is returned. A reference list is not allowed inside an array expression.

So, it is documented as a non-backwards-compatible feature. I don't know if it should be possible to fix older documents by replacing the ";" with a "~".