Summary: | Custom validation breaks after saving (FILEOPEN issue?) | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | mendelyev37 |
Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | UNCONFIRMED --- | ||
Severity: | normal | CC: | erack, stephane.guillou |
Priority: | medium | ||
Version: | 7.3.7.2 release | ||
Hardware: | All | ||
OS: | Linux (All) | ||
Whiteboard: | |||
Crash report or crash signature: | Regression By: | ||
Bug Depends on: | |||
Bug Blocks: | 88175 |
Description
mendelyev37
2023-01-26 10:15:13 UTC
In Excel, the formula in cell =IF(ISNUMBER(A1); AND(INT(A1)=A1; A1 >= 10; A1 <= 20); COUNTIF({"NC";"ND"}; A1)>0) gives error at entry time: > There's a problem with this formula. > > Not trying to type a formula? > When first character is an equal ("=") or minus ("-") sign, ... and you simply can't have it, until you replace the {"NC";"ND"} with a range reference like C1:C2. Saving a document with such a validation and a cell formula as XLSX from Calc, and trying to open in Excel, gives a warning first: > We found a problem with some content in 'Countif.xlsx'. > Do you want us to try to recover as much as we can? and is confirmed, then > Excel was able to open the file by repairing or removing the unreadable content. > Removed Feature: Data validation from /xl/worksheets/sheet1.xml part > Removed Records: Formula from /xl/worksheets/sheet1.xml part So it looks like an inherent Excel limitation, and indeed a limitation of its file format. However, it is unclear if the export in this form is satisfactory: it looks like the result is considered invalid - so maybe we need to do something to make it valid (even if this means "to drop content")? Thank you for your quick reply. I completely missed that point where Excel doesn't handle collections for COUNTIF. In my case, replacing the string collection by a range is not possible as I can't modify cell content. My .xlsx files being automatically generated, I dropped COUNTIF({'A', 'B', ...})>1 for OR(A1='A', A1='B', ...) and it solved my issue. I don't know what would be best fit to avoid such surprise when exporting to .xslx. Here are just few ideas: - automatically replace COUNTIF(collection, criteria) by a SUM(value1=criteria, value2=criteria, ...)? Not sure it works in all scenarios - trigger a dedicated warning if using COUNTIF(collection, criteria)? - simply warn user when reopening an xlsx file with Calc as Excel is already doing? |