Hi, I've been trying to set a custom validation on a cell with the following procedure : 1) Create a new calc file 2) Select the A1 cell 3) Go to Data > Validity... 4) Allow=Custom / Keep "Allow empty cells" checked 5) Set formula to : IF(ISNUMBER(A1), AND(INT(A1)=A1, A1 >= 10, A1 <= 20), COUNTIF({"NC","ND"}, A1)>0) Therefore, I am allowed to put in A1 cell an integer between 10 and 20 OR a string among NC or ND. However, as soon as I do the following, validation doesn't work anymore : 1) Delete the content (only) of A1 cell (the validation rule is still applied) 2) Save the file in .xlsx format 3) Close and reopen it 4) Try to put NC or ND in the A1 cell -> refused 5) Try to put an integer between 10 and 20 -> accepted I manage to make it work again by resetting the validation formula (ie: deleting and rewriting one character in the formula and click OK). But it has to be done after each opening. Therefore I assume there is something missing during FILEOPEN? I get the same result with v7.3.7.2 and v7.4.3.2
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?