Download it now!
Bug 108673 - FILESAVE XLSX: Copy-pasting cell validation may result in a sheet reference error saved as invalid xlsx content
Summary: FILESAVE XLSX: Copy-pasting cell validation may result in a sheet reference e...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.0.0.beta2
Hardware: All All
: medium normal
Assignee: Thorsten Behrens (CIB)
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX Cell-Reference
  Show dependency treegraph
 
Reported: 2017-06-21 12:24 UTC by Gabor Kelemen
Modified: 2020-07-10 11:46 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Example source file (9.64 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-21 12:24 UTC, Gabor Kelemen
Details
Cells with validation pasted to the first sheet (9.08 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-06-21 12:27 UTC, Gabor Kelemen
Details
Cells with validation pasted to the first sheet - in XLSX format (4.77 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-06-21 12:27 UTC, Gabor Kelemen
Details
Original cell and its pasted version in a new file (116.61 KB, image/png)
2017-06-21 12:28 UTC, Gabor Kelemen
Details
Excel 2013 reacts with an error to opening the attached XLSX (47.62 KB, image/png)
2017-06-21 12:29 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2017-06-21 12:24:23 UTC
Created attachment 134188 [details]
Example source file

Attached file has two sheets, with cell validation in the B1 cells.

When copy-pasting the B1 cell from both sheets to the firsd sheet of a new spreadsheet document, the sheet reference part of the validation source becomes a #REF! error: $Sheet2.$A$1:$A$3 becomes $#REF!.$A$1:$A$3

This may make some sense in itself, as there is no second sheet in the new document. Adding a new sheet and pasting there gives correct results.

The main problem is that the validation source with #REF! error can be saved to both ODS and XLSX format, and in the case of XLSX, Excel 2013 considers it an invalid file.
Comment 1 Gabor Kelemen 2017-06-21 12:27:12 UTC
Created attachment 134189 [details]
Cells with validation pasted to the first sheet

In A2 we can see the #REF! error.
Comment 2 Gabor Kelemen 2017-06-21 12:27:40 UTC
Created attachment 134190 [details]
Cells with validation pasted to the first sheet - in XLSX format
Comment 3 Gabor Kelemen 2017-06-21 12:28:31 UTC
Created attachment 134191 [details]
Original cell and its pasted version in a new file
Comment 4 Gabor Kelemen 2017-06-21 12:29:05 UTC
Created attachment 134192 [details]
Excel 2013 reacts with an error to opening the attached XLSX
Comment 5 Buovjaga 2017-07-01 18:33:04 UTC
Repro.

Win 8.1 32-bit
MSO 2013
LibreOffice Version: 6.0.0.0.alpha0+
Build ID: cac5c9f6081590b0548d3116bc4cd4a2509ec576
CPU threads: 4; OS: Windows 6.29; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2017-07-01_00:41:48
Locale: fi-FI (fi_FI); Calc: group
Comment 6 QA Administrators 2018-07-04 02:49:26 UTC Comment hidden (obsolete)
Comment 7 Eike Rathke 2020-07-10 10:53:09 UTC
(In reply to Gabor Kelemen from comment #0)
> The main problem is that the validation source with #REF! error can be saved
> to both ODS and XLSX format, and in the case of XLSX, Excel 2013 considers
> it an invalid file.

For ODFF the #REF! is perfectly fine, see
https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#References

What does Excel save instead for an invalid reference?