https://bugs.documentfoundation.org/show_bug.cgi?id=99313
Bug ID: 99313 Summary: Importing Excel file with installed validity check based on a formula produces faulty result Product: LibreOffice Version: unspecified Hardware: All OS: Windows (All) Status: UNCONFIRMED Severity: normal Priority: medium Component: Calc Assignee: libreoffice-bugs@lists.freedesktop.org Reporter: j...@mimuw.edu.pl 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 5.1.1.3 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 -- You are receiving this mail because: You are the assignee for the bug.
_______________________________________________ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs