Bug 125914 - Validation with cells referencing themselves always gives error
Summary: Validation with cells referencing themselves always gives error
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Validity
  Show dependency treegraph
 
Reported: 2019-06-14 00:08 UTC by Aron Budea
Modified: 2019-11-21 13:17 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample XLSX (8.66 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-06-14 00:08 UTC, Aron Budea
Details
Example file from Calc (11.89 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-10-03 11:55 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Aron Budea 2019-06-14 00:08:07 UTC
Created attachment 152179 [details]
Sample XLSX

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:
- 645789
- -1956.56
- 1999-08-31
- 23:59

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 6.4.0.0.alpha0+ (95462a02a3aee1e3e7f9aa8fc50ba25fee3fa592), 4.0.0.3 / Windows 7.
Comment 1 m.a.riosv 2019-06-14 07:54:29 UTC
What seems a bug to me it's accepting a value different than the value in the referenced cell, not complying the criteria.
Comment 2 NISZ LibreOffice Team 2019-10-03 11:55:55 UTC
Created attachment 154728 [details]
Example file from Calc

Reproduced in Calc, without involving an XLSX file:

Version: 6.4.0.0.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
Calc: CL

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.
Comment 3 NISZ LibreOffice Team 2019-10-03 11:57:12 UTC
Confirming and removing xlsx-related meta/keyword.
Comment 4 m.a.riosv 2019-10-03 13:38:23 UTC
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.
Comment 5 Aron Budea 2019-10-04 23:53:22 UTC
(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?
Comment 6 m.a.riosv 2019-10-05 10:18:11 UTC
I don't understand the reasons, and only compatibility it's no one for me.
Comment 7 Aron Budea 2019-10-05 13:11:01 UTC
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.
Comment 8 Aron Budea 2019-10-16 12:36:55 UTC
@m.a.riosv: does the issue require further clarification?
Comment 9 m.a.riosv 2019-10-16 20:30:33 UTC
Sorry, I can't agree this is a bug, but if it is for you, don't worry, mark it as new.