Created attachment 152179 [details]
Open the attached spreadsheet, created in Excel.
In the B column, next to the description validation criteria is set with "equal to" setting that refers to the cell itself. This means only the basic criteria is taken into account (whole number, decimal, date, time), and any such entry can be entered.
Enter values like:
These are all allowed in Excel, but none of them is allowed in Calc. Note that in Excel the value criteria can be a cell reference, however Calc shows cell references as values (eg. "B1" instead of "=B1"), and doesn't allow selecting a referenced cell.
Observed using LO 188.8.131.52.alpha0+ (95462a02a3aee1e3e7f9aa8fc50ba25fee3fa592), 184.108.40.206 / Windows 7.
What seems a bug to me it's accepting a value different than the value in the referenced cell, not complying the criteria.
Created attachment 154728 [details]
Example file from Calc
Reproduced in Calc, without involving an XLSX file:
Version: 220.127.116.11.alpha0+ (x86)
Build ID: c45d477b0a0038d9c25176cf7cff299e5ddf3a7a
CPU threads: 4; OS: Windows 6.3; UI render: default; VCL: win;
TinderBox: Win-x86@42, Branch:master, Time: 2019-09-30_05:06:55
Locale: hu-HU (hu_HU); UI-Language: en-US
It is possible in the editor to refer to the same cell and it does not work. This is set here in column B.
The same validity condition works if it refers to a different cell. This is set in column D, referring to column C.
Confirming and removing xlsx-related meta/keyword.
Confirming what, what is the bug?.
Sure there is something that I miss, here.
Change the value of a cell doesn't validate against itself, what it's wrong?, the opposite works as if no validation, so for what is needed the validation, in such case?.
I don't see the bug assigned, the way it's other person confirm the bug, not the reporter.
(In reply to NISZ LibreOffice Team from comment #2)
> Reproduced in Calc, without involving an XLSX file:
Indeed, I was thinking the whole thing was unsupported in Calc, but since the cell reference works, there's no reason why self-reference shouldn't work.
(In reply to m.a.riosv from comment #4)
> Change the value of a cell doesn't validate against itself, what it's
> wrong?, the opposite works as if no validation, so for what is needed the
> validation, in such case?.
The point of self-referring the cell is that you only want to allow the entry of a specific type of data (ie. whole numbers, decimals etc.), but want to put no further restriction on it. There's no such option in the "Data" dropdown, so it can't be done in a different way AFAIK.
And even if there's another way, this is an interop issue between Excel and Calc, because Excel accepts any entries in a cell with self-referencing validity criteria, as long as it conforms to the selected type of data. You can enter data in such cell in Excel, but not in Calc, what is that if not an issue?
I don't understand the reasons, and only compatibility it's no one for me.
Please read both of my previous explanations:
(In reply to Aron Budea from comment #0)
> "equal to" setting that refers to the cell itself. This means only the basic
> criteria is taken into account (whole number, decimal, date, time), and any
> such entry can be entered.
(In reply to Aron Budea from comment #5)
> The point of self-referring the cell is that you only want to allow the
> entry of a specific type of data (ie. whole numbers, decimals etc.), but
> want to put no further restriction on it. There's no such option in the
> "Data" dropdown, so it can't be done in a different way AFAIK.
Trying once again with an explanation. If you set it to eg. whole number this way, only a whole number can be entered in the cell. It's a way to only allow certain type of data to be entered, without having to specify further, unnecessary restrictions.
@m.a.riosv: does the issue require further clarification?
Sorry, I can't agree this is a bug, but if it is for you, don't worry, mark it as new.