Created attachment 168773 [details] 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.
Reproduced with file. Also with oldest of 6.3 Linux bibisect repo and 3.3.0 on Windows 10. Arch Linux 64-bit Version: 7.4.0.0.alpha0+ / LibreOffice Community Build ID: ac21d937690c1ef995df22e11384e0fab226472e CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb) Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded Built on 2 December 2021
Created attachment 184928 [details] Slidghtly modified example file before saving as XLSX
Created attachment 184929 [details] Slidghtly modified example file after saving as XLSX and reloading