Bug 99313 - FILEOPEN: XLSX - Validity check based on a formula produces faulty result
Summary: FILEOPEN: XLSX - Validity check based on a formula produces faulty result
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
Whiteboard: TooBusyNowNeedsFurtherTestingForPossi...
Keywords: filter:xlsx
Depends on:
Blocks: Cell-Formula XLSX
  Show dependency treegraph
Reported: 2016-04-15 05:42 UTC by Jerzy Tyszkiewicz
Modified: 2020-10-02 15:40 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:

The Excel spreadsheet described in the bug report (7.68 KB, application/xlsx)
2016-04-15 05:44 UTC, Jerzy Tyszkiewicz
The example file in Calc 6.1 and 6.2 (91.87 KB, image/png)
2020-10-02 15:39 UTC, NISZ LibreOffice Team

Note You need to log in before you can comment on or make changes to this bug.
Description Jerzy Tyszkiewicz 2016-04-15 05:42:02 UTC
User-Agent:       Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.110 Safari/537.36 OPR/36.0.2130.65
Build Identifier: LibreOffice

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. 

Reproducible: Always

Steps to Reproduce:
1. Import the described Excel file.
2. Observe the "Validity" tool.
3. Attempt to insert values.
Actual Results:  
As described above, some values are permitted, some are rejected, is a more-or-less unpredictable way.

Expected Results:  
1. Optimal choice: implement validity check by a formula to work correctly.
2. Suboptimal, but reasonable choice: display a warning upon opening file that some features will be missing, ignore the unsupported validity chcek completely.

[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no

Reset User Profile?No
Comment 1 Jerzy Tyszkiewicz 2016-04-15 05:44:37 UTC
Created attachment 124353 [details]
The Excel spreadsheet described in the bug report
Comment 2 Buovjaga 2016-04-26 10:02:01 UTC
Reproduced with document.

Win 7 Pro 64-bit Version:
Build ID: d848960a3e77a8608a48f3ba394928c955f1e2d9
CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-04-25_06:03:51
Locale: fi-FI (fi_FI)
Comment 3 QA Administrators 2017-05-22 13:39:52 UTC Comment hidden (obsolete)
Comment 4 Jerzy Tyszkiewicz 2017-05-22 16:39:34 UTC
The bug remains unchanged.

LibreOffice Version:
Build ID: 3d9a8b4b4e538a85e0782bd6c2d430bafe583448
CPU Threads: 4; OS Version: Windows 6.2; UI Render: default; Layout Engine: new; 
Locale: pl-PL (pl_PL); Calc: group
Windows 10 64 bit.
Comment 5 Jerzy Tyszkiewicz 2017-05-22 17:05:15 UTC
The bug existed already in LibreOffice 3.3.0
Comment 6 QA Administrators 2018-07-03 02:39:22 UTC Comment hidden (obsolete)
Comment 7 QA Administrators 2020-07-03 03:40:36 UTC Comment hidden (obsolete)
Comment 8 NISZ LibreOffice Team 2020-10-02 15:39:23 UTC
Created attachment 166041 [details]
The example file in Calc 6.1 and 6.2

This seems to work since 6.2.0