Bug 37872 - named cells with periods ref/name errors
Summary: named cells with periods ref/name errors
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.0 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Markus Mohrhard
Whiteboard: target:3.4.1
Depends on:
Reported: 2011-06-02 23:43 UTC by Jasper Jongmans
Modified: 2012-03-24 11:35 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

example document demonstrating issue (10.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-06-02 23:43 UTC, Jasper Jongmans

Note You need to log in before you can comment on or make changes to this bug.
Description Jasper Jongmans 2011-06-02 23:43:17 UTC
Created attachment 47467 [details]
example document demonstrating issue

What I did:
Define cell names with periods such as "mod.ac" and reference them in formulas.

What happens:
Certain cell names containing a period yield #REF!-errors, which turn into #NAME!-errors when the document is saved and reloaded.

What I expected:
The value of the referenced named cell to be used in the formula.

Referencing "=foo.abc" turns in to "='file:///C:/Users/Aprogas/Documents/foo'#foo.ABC#REF!" whereas referencing "=abc.def" works fine. For more examples see attached file. Third column shows in plaintext what I entered in second column to yield that result.

Issue tested and occurs on LibO 3.3.2 and LibO 3.4 RC2, issue also tested but does not occur on OO 3.2.0 and OO 3.3.0.
Comment 1 Oliver Brinzing 2011-06-03 07:39:54 UTC
Comment 2 Markus Mohrhard 2011-06-06 20:48:07 UTC
I have a look at this.
Comment 3 Markus Mohrhard 2011-06-09 02:52:32 UTC
Ok, all references where the part after the point can be interpreted as an column address will be used as external reference.

I have to think a bit about a workaround and talk to Kohei.
Comment 4 Jasper Jongmans 2011-06-09 09:56:02 UTC
Is there ever a need to refer to just a column without row number/range? "=ac" isn't interpreted as a column or cell reference, but "=ac42" is a cell reference; the same logic could apply to e.g. "=foo.ac" and "=foo.ac42". One could even go so far that when "foo.ac42" would throw a ref-error (e.g. sheet foo does not exist) but a label "foo.ac42" does exist, it is interpreted as the label rather than the ref-error. It might also be useful to warn users who enter labels with ambiguous meaning.
Comment 5 Markus Mohrhard 2011-06-09 11:19:19 UTC
Ok, after some discussion with Kohei we agreed that we won't allow points in named ranges any more. There are some additional problems we solve with this step.

We will adjust the import filter as well. Old named ranges with points will be changed during the import.
Comment 6 Rainer Bielefeld Retired 2011-06-10 02:58:37 UTC
RC2 is bit by bit identical with release version, so separate items in the version picker are useless. Changes have been discussed with Michael Meeks.
Comment 7 Kohei Yoshida 2011-06-10 07:53:21 UTC
I've signed off and pushed Markus' patch to the -3-4 branch.  The fix will be in 3.4.1.
Comment 8 Gerhard Blab 2012-02-28 06:33:12 UTC
I am running into exactly the same problem as Jasper, namely that after importing and filling in an xls file, and saving it as ods, any re-open of the file yields #REF! errors when cell names with periods are being accessed.

This error was supposed to be solved in 3.4.1, but it still occurs on 3.4.5 (linux, x86_64).
Comment 9 Gerhard Blab 2012-02-28 06:35:42 UTC
small correction: original file was of type .xltx
Comment 10 Rainer Bielefeld Retired 2012-03-24 10:54:41 UTC
T checked on WIN7 with sample document and OOo 3.3.1, OOo 3.3, LibO 3.3.0 and various other versions until 3.5.2, all show "#NAME! and "='file:///C:/Users/...." as cell contents in column B

Any idea what we are observing here?
Comment 11 Markus Mohrhard 2012-03-24 11:35:21 UTC
This test docuemnt is invalid according to ODF1.2 and therefore we are no able to correctly deal with it.

ODF1.2 requires that a.bc is a cell reference when bc can be interpreted as a column. Therefore we changed our range name dialog s and formula engine to prevent the creation of range names with points.

You can check that it is not possible to create new range names with points but I fear that it is not easily possible to correct these files during import because our formula engine was never able to deal with these range names.

I know that OpenOffice and AOO are still able to create these files but there is no sane way to handle these formulas. They are invalid according to OpenFormula!