Q&A thread: https://ask.libreoffice.org/en/question/233184/
In pursuit of the mentioned thread I first noticed that the Validity feature got the new Allow:>Custom>Formula variant.
I tested it (though I judge it to be completely useless; 'Cell range' option always allowed to enter a formula, and this is sufficiently made clear there) and found with version 188.8.131.52 and with 184.108.40.206 as well that the new feature doesnt't work as requested and expected:
The formula entered under >Custom>Formula is passed to the editable line under the 'Cell range' path, but isn't put in force as long as it not got at least a fake edit IN THIS PLACE.
The new setting will also work as soon as the document was saved to a file. That's the reason fo what I cannot provide an attachment showing the issue.
Instead of a demonstrating attachment:
Create a new Calc document, use first sheet e.g.
Enter a, b, c, d into <Cells A2 through A5.
Go to cell D2.
Go to 'Formula'
Selecting D2 results in the dropdown symbol shown right of it.
Clicking on it offers a list containing 2 rows with b and c.
No such functionality.
Open the Valdity dialog for DE2 again.
Call >Critria>Cell range
Nothing shown in 'Source'
'Source' is containing the correct ecpression.
Store the document to a file
Now the Validity setting works as originally expected
(Same thing if the range expression was edited in any way under 'Source' without changing it.)
See also tdf#130738.
My suggsetion: Make sure that formulas entered in 'Cell range' mode are treated properly by the Excel filters when importing/exporting, and remove it from Calc again OR cancel the redundancy concerning 'Cell range'. First alternative clearly preferred.
Thank you for reporting this bug. I can reproduce this partially, but not fully in
Version: 220.127.116.11.alpha1+ (x86)
Build ID: ec7374ff84c71edfbb30d6e4dc5b486b6df7107f
CPU threads: 2; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win;
TinderBox: Win-x86@42, Branch:master, Time: 2019-11-10_21:37:30
Locale: en-US (en_US); UI-Language: en-US
when I Create a new Calc document,
Enter a, b, c, d into <Cells A2 through A5>.
Go to cell D2.
Go to 'Formula'
and click OK, No dropdown symbol shown right of it.
But, again when I Open the Validity dialog for DE2 again.
Cell>Criteria>Cell range , 'Source' is containing the correct expression.
Even without saving the document and reopening it, Now the Validity setting works as originally expected.
(In reply to Durgapriyanka from comment #1)
> Thank you for reporting this bug. I can reproduce this partially, but not
> fully in
> Cell>Criteria>Cell range , 'Source' is containing the correct expression.
> Even without saving the document and reopening it, Now the Validity setting
> works as originally expected.
This happened to me too, but only if I had applied at least an as-if action of editing by pressing 'OK' instead of 'Cancel'.
confirming as per comment #1
(In reply to Wolfgang Jäger from comment #0)
> ... I first noticed that the Validity feature
> got the new Allow:>Custom>Formula variant.
> ... I judge it to be completely useless; 'Cell range' option
> always allowed to enter a formula, and this is sufficiently made clear
Only answering the cited part.
Since comment 0 had mentioned tdf#130738, it's not quite clear to me why could you "judge it to be completely useless". tdf#130738 links to two Q&As with explanation what "Custom" is for; and it's sufficiently different from "Cell range". The latter is used to provide a *list of allowed values*. The "Custom" is for an arbitrary formula that returns TRUE or FALSE that is the result of validity check. E.g., it can check if the value matches some regex. IIUC thic can't be accomplished using 'Cell range' formula.
So the problem here seems that the two formula areas ("Custom" and "Cell Range") are not separated and checked strictly.
1. The formulas in the two areas are expected to be different. The "Custom" formula should result in a scalar value that would be converted to boolean for the purpose of the check. The "Cell Range" expects to return a range which would be used as a source to find a match for the value in the cell in the validity check. But when you enter the formula, it's not checked that its result matches the area.
2. A formula entered in "Custom", which gives a range, will automatically end up in "Cell Range" (but the drop-down will not appear immediately). This automatic "migration" could be unexpected (?).
IMO it's OK to have it migrated as it is now, but the check that it's in fact a range formula should be made at the stage of dialog closing, and the actions should be performed according to its final type (range result detected => Cell Range validity rule => create a list).
(In reply to Mike Kaganski from comment #5)
> ... but the check that it's in fact a range formula ...
sorry for the wrong use of "range formula" term here; I only meant "a formula returning a range".