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.
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.
I have a look at this.
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.
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.
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.
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.
I've signed off and pushed Markus' patch to the -3-4 branch. The fix will be in 3.4.1.
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).
small correction: original file was of type .xltx
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?
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!