[Libreoffice-bugs] [Bug 139499] New: FILESAVE: mangled structured references in Data Validity cell range criterion

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=139499

            Bug ID: 139499
           Summary: FILESAVE: mangled structured references in Data
                    Validity cell range criterion
           Product: LibreOffice
           Version: 7.0.4.2 release
          Hardware: x86-64 (AMD64)
                OS: Windows (All)
            Status: UNCONFIRMED
          Severity: minor
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: jocn at protonmail.com

Created attachment 168773
  --> https://bugs.documentfoundation.org/attachment.cgi?id=168773&action=edit
OOXML spreadsheet created in Libre Calc exhibiting the issue

LibreOffice Calc has a feature to restrict the values of a cell to the values
present in a specified cell range (the "source"), by way of the Data ->
Validity... modal.

The source can be specified using the usual cell reference syntax without
issue.
The source may also be specified using a structured reference to a database
range. I have encountered issues with the persistence of these structured
references after saving and loading a document.

In the OOXML and 2007-365 Excel formats, the structured reference's column name
is not saved in a case-sensitive manner. The table name is saved correctly, but
the column name is saved in lowercase. Since column names are case sensitive,
the structured reference breaks, and only Err. 509 is available from the
dropdown menu.

In ODF the structured reference decays to an absolute cell reference. This is
also not ideal, because a spreadsheet author might expect the validation to
change when the database range is redefined. 

To reproduce, create and populate a database range with column headers, say
table Foo with field Bar (uppercase B). Then, for some cell outside the table,
perhaps B9, specify a cell range data validation using the syntax Foo[Bar].
Save the document, restart Calc and reopen the document, and check the validity
of B9. I have done this in the attached OOXML spreadsheet.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210108/37ac3d3c/attachment-0001.htm>