Bug 90324 - xlsx import : Text formatted custom validated cell does not allow entry of valid numbers;support of formula in validation
Summary: xlsx import : Text formatted custom validated cell does not allow entry of va...
Status: RESOLVED DUPLICATE of bug 96698
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.1.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-Limitations Cell-Validity
  Show dependency tree / graph
 
Reported: 2015-03-29 15:06 UTC by Dennis Francis
Modified: 2020-06-29 14:09 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Attachments
xlsx file with text formatted cell having custom validation (7.73 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-03-29 15:06 UTC, Dennis Francis 		Details
excel 2010 (10.22 KB, image/png)
2015-03-31 20:00 UTC, raal 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Dennis Francis 2015-03-29 15:06:15 UTC 
Created attachment 114445 [details]
xlsx file with text formatted cell having custom validation

Steps to reproduce

1. Open the attached xlsx file in calc
2. Edit cell C6 and enter the number 0.34
3. Calc cell validator error popup comes up saying that entry is invalid even though the entry is valid per the custom validation formula set in Excel.

I can confirm that this problem does not happen when editing this file in Excel 2013.

The custom validation formula for cell C6 is :

=AND(VALUE(C6)>=0,VALUE(C6)<100,(LEN(C6)-FIND(".",C6,1))=2,ISNUMBER(VALUE(RIGHT(C6,1))),ISNUMBER(VALUE(LEFT(C6,1))))

Tested OSes : Fedora 21, Windows 7
Comment 1 raal 2015-03-31 20:00:30 UTC 
Created attachment 114505 [details]
excel 2010
Comment 2 raal 2015-03-31 20:16:15 UTC 
(In reply to raal from comment #1)
> Created attachment 114505 [details]
> excel 2010

In my language is delimiter comma ;  (LEN(C6)-FIND(".",C6,1))=2 here is delimiter dot
Comment 3 raal 2015-04-01 11:08:26 UTC 
I can confirm with LO 4.4.1.2, win7
I changed formula to accept delimiter "," and this doesn't work in LO.

I doesn't see formulas in data validation help, so I think it's not suppported. Marking as enhancement.

https://help.libreoffice.org/Calc/Criteria
Comment 4 Justin L 2019-02-28 11:00:59 UTC 
This should be fixed in LO 6.2, thanks to bug 96698. I tested percent-validation.xlsx and it accepted 0.11, 0.24 and 0.99. Perhaps the original responders can test their specific expectations and report back on whether they also consider it to be fixed.
Comment 5 NISZ LibreOffice Team 2020-06-29 14:09:10 UTC 
Duplicate per last comment. Also works in current versions, sans the locale-specific difference in decimal separator. But that is also there in Excel.

*** This bug has been marked as a duplicate of bug 96698 ***