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 6.2.5.2 and with 6.4.2.1 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. Call >Data>Validity Open >Criteria>Custom Go to 'Formula' Enter OFFSET($A$2;1;0;2;1) OK Expected: Selecting D2 results in the dropdown symbol shown right of it. Clicking on it offers a list containing 2 rows with b and c. Observed: No such functionality. Open the Valdity dialog for DE2 again. Call >Critria>Cell range Extected: Nothing shown in 'Source' Observed: 'Source' is containing the correct ecpression. ... Store the document to a file Expected: Nothing new Observed: 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: 6.4.0.0.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 Calc: threaded when I Create a new Calc document, Enter a, b, c, d into <Cells A2 through A5>. Go to cell D2. Call >Data>Validity Open >Criteria>Custom Go to 'Formula' Enter OFFSET($A$2;1;0;2;1) 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 > there 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".
Dear Wolfgang Jäger, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Tested with V 7.4.3.2 under Win 10 Behavior unchanged Concerning my comment "judge {the new feature} to be completely useless" I learned from Comment #4 by @Mike Kaganski (who expressly told to only comment on my quoted judgement) that I hadn't correctly understood the purpose of that feature. Having readjusted my perception, however, there was no reason to withdraw the report. The bug was a bug - as described. It's still living.
In addition: Based on my updated understanding parts of my original expectations are no longer valid, and the chosen subject is misleading. It should better read: Validity>Custom>Formula wrongly copies an improper entry to the 'Source' field under the 'Allow: Cell Range' option. Evaluation then only after additional trigger. Appending: After copying to 'Cell Range', and also after save/reload the formula is shown in both places (Cell RFange > Source: and Custom > Formula). This makles no sense, imo. Any informed person my change the subject as suggested (or similarly) with my consent. I didn't do it myself because I very rarely used '>Data>Validity', don't feel suer if my understanding of the new feature is now sufficient, and have no access to competing software probably having induced the change as a compatibilty issue.