Issue 126926 - Importing Excel file with installed validity check based on a formula produces faulty result
Summary: Importing Excel file with installed validity check based on a formula produce...
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: 4.1.2
Hardware: All Windows 7
: P5 (lowest) Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2016-04-15 06:04 UTC by J.Ty.
Modified: 2016-04-15 06:20 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Excel file used in the report (7.68 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-04-15 06:04 UTC, J.Ty.
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description J.Ty. 2016-04-15 06:04:39 UTC
Created attachment 85435 [details]
Excel file used in the report

I have produced an Excel spreadsheet with a validity check for cells. It concerns column A. 
Cell A1 is the column header.
Cell A2 has validity check by formula: =COUNTIF(A$1:A1;A2)=0. According to Excel, only values inserted in A2 which amke this formula produce TRUE are permitted. 

Cells A2:A10 are copied down from A1, so that each cell contains a check with a formula counting the present value among all cells above itself.
Effectively, in Excel cells A2:A10 have a validity check that requires that the value in a cell is not a duplicate of a value above it. Entering a duplicate produces a warning message and is rejected, as expected.

LibreOffice does not permit validity check by formula. However, if I download the file into LibreOffice, the formula from the Excel file somehow gets into the validity check and causes it to operate in a way difficult to explain. 

First of all, "Validity" reports "Allow all values", but there is a (grayed out) restriction to a valid range, with the formula from Excel shown as the minimum value, 0 being the maximum.

Next, entry of values into the affected cells is restrcted in a very unpredictable way. 
- one can enter 0 values one by one, starting from top.
- one can enter 1 values one by one, starting from top.
- if one enters a few 0 values, and then a single 1, this input is rejected.
- if one enters a few 1 values, and then a single 0, this input is permitted.
- Entering 2 and string "a" is always rejected, not matter where and if there are other value sin the cells A2:A10 or not.
Comment 1 J.Ty. 2016-04-15 06:20:06 UTC
I have noticed that the above report talks about LibreOffice, while I meant OpenOffice. Indeed the bug is identical in both tools and I have filed two reports.