I have searched for it but couldn’t find even a little mentioning about this one. There is a method in Microsoft Excel by which you write a formula to the data validation options like: =COUNTIF(A:A; A2)=1. This way, if you enter a value which is identically existing on the same column, it won’t accept it.
Onlyoffice desktop editor has nearly exact same settings for data validation with Microsoft Excel. But this formula doesn’t work in Onlyoffice. It just denies every single entry no matter what you type in. I guess it compares the value of the cell before editing. This might be a bug.
For example if there are already identical values in two cells, double click one of them and hit enter, it will warn you that it is a duplicate but if you edit another cell which already has unique data, it won’t warn. This is expected. But new entries will always warn that it is a duplicate. This behavior led me thinking it compares the static pre-edit value. And since the new entries’ pre-edit value is always blank, I guess it recognizes it a duplicate because there are other blank cells. Checking or unchecking “ignore spaces” doesn’t change this behavior.
So, is this a bug or intended behavior and is there a way to prevent duplicate entries?
Since my Onlyoffice installation is not English, a video may not help well. And since I am a new user, I can’t upload files but that’s not important for the matter here. I am using the most up to date version as of now. v8.0.0.99
To reproduce, just create a new file with the entries like shown in the screenshot below, a few names matching cities. Then select column B and click Data → Data Validation. Select “Other”, type in this formula: =COUNTIF(B:B;B1)=1 click ok.
The expected behavior is that it won’t allow if you enter an already existing content on B column. If you do exact same steps in Microsoft Excel, it works as expected. This ensures all the data in each cell on column B is unique.
But in Onlyoffice, it behaves unpredictably. Like sometimes it allows duplicates but most of the times it won’t allow even the unique values. See the screenshot:
Here is something interesting; try it couple of times, it may allow once in a while. Or try filling the corresponding A column first, then it will allow the same entry which it didn’t allow before (not only it will allow unique values but sometimes also the duplicate values).
In Microsoft Excel, the same formula is giving the same consistent result always. Meaning it allows unique entries regardless what are there in other columns and it doesn’t allow if the entry is a duplicate. (ın fact you don’t need to create a new file. Just open the same file in Microsoft Excel and it works as expected consistently on each entry.)
In any case, if this is a bug (which seems to be), it needs to be resolved and if it’s not a bug, then I need a way to disallow duplicate data entry on a column and I am kindly asking how
Dear @TurabG
We have just released Document Server v8.1. This release contains a fix for the situation described earlier in this thread. We are going to release the desktop editors v.8.1 as soon as possible.
