In Excel (all the time) there is in Data => Validity an additional item to select: "Custom" https://support.office.com/en-nz/article/Apply-data-validation-to-cells-c743a24a-bc48-41f1-bd92-95b6aeeb73c9#bm4_8 Checking for values of other cells (or the actual content of the active cell) can be done in LibreOffice also with a formula in the Minimum/Maximum values. But 2 problems left: 1. A general valitdity check for the entered new content of the active cell - especially for text - with a custom formula like in Excel is not possible. A sometimes painful shortcoming :-( 2. Excel compatibility: As there is no "Custom" formula functionality in LO, the Excel import and re-export must kill this (traditional) Excel functionality.
Sounds fair -> NEW.
Bug is still present. No changes in bug behavior. Testsystem: LO 5.2.3.3 on Windows Vista
*** Bug 102273 has been marked as a duplicate of this bug. ***
Created attachment 134044 [details] customValidation.xlsx: a couple of scenarios for testing custom validation code pointers -xlsx import: sc/source/filter/oox/worksheethelper.cxx -xls import: sc/source/filter/excel/xicontent.cxx -validation: sc/source/core/data/validat.cxx ::IsDataValid() -validation: sc/source/core/data/conditio.cxx ::IsValidStr()
Created attachment 134080 [details] partial fix: custom formulas work as long as they don't reference the edited cell. Currently, custom formulas do not work at all. The only time it MIGHT work out is working with non-zero numbers. The larger problem here is that the formula runs on the previous data, so whatever you typed is not being checked. So using my example document, if you type "comma,", then it will be accepted (since it evaluated an empty cell), but anything you enter after that will be rejected (because it is evaluating the old value of "comma,"). I think that is what this comment is trying to indicate. // for Custom, it must be eOp == SC_COND_DIRECT //TODO: the value must be in the document !!! It is easy to ensure that for Custom, eOp == SC_COND_DIRECT. That is why the patch does for .xls[xb] and .xls. I'm not submitting it on it's own though because the formula will almost always be checking it's own value, so the TODO part is an essential part of any fix.
Marco Cecchetti committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f15c4b0f3c2cdfa7a72c97301ff32cc1764c54da tdf#96698 - calc: add support for custom validation (through a formula) It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I love it. Verified in 6.2 alpha using my personal TSC problem document. Thanks for sticking with this Marco.
Zdeněk Crhonek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1394bad8ce5c5e341f824fd6cd14769ada32eeb3 uitest for bug tdf#96698 , Validity-> Custom It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
This is a very important fix - should be documented in the changelog: https://wiki.documentfoundation.org/ReleaseNotes/6.2#Calc
(In reply to burnuser from comment #9) > This is a very important fix - should be documented in the changelog: > https://wiki.documentfoundation.org/ReleaseNotes/6.2#Calc Feel free to do it yourself :)
(In reply to burnuser from comment #9) > This is a very important fix - should be documented in the changelog: Done (since I already have an account for the wiki). Added "Data Validation now supports custom formulas (Marco Cecchetti - Collabora) tdf#96698"
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 respective control under the 'Cell range' path, but isn't put in force as long as it not got at least a fake edit. (The feature also is not mentioned in the online helöp for 6.4) 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.
(As a clarification to Wolfgang Jäger from comment #12) > ... Wanted to say: > The formula entered under >Custom>Formula is passed to the respective > control under the 'Cell range' path, but isn't put in force as long as it > not got at least a fake edit IN THE 'Cell range' EDIT LINE.
Wolfgang, thanks for the comment, please open a new bug report with it, and reference this one in the 'See also' field.
(In reply to Aron Budea from comment #14) > Wolfgang, thanks for the comment, please open a new bug report with it, and > reference this one in the 'See also' field. Isn't this unnecessarily "bureaucratic"? I well noticed that my hints concerning the missing functionality didn't fit exatly under the subject. There are many bug reports, however, basically matching their subject even less. Surley user "burnuser" didn't just want a fake feature, but a working one, and insofar the bug (enhancement) isn't resolved. It would cost me another half hour probably to create an extra report, and there are already at least two more important ones I already deferred. (I'm not as fast as you, obviously.) (I may surrender anyway.)
(In reply to Wolfgang Jäger from comment #15) > (In reply to Aron Budea from comment #14) > > Wolfgang, thanks for the comment, please open a new bug report with it, and > > reference this one in the 'See also' field. > > Isn't this unnecessarily "bureaucratic"? No. This is an enhancement report. The commit referenced in comment 6 adds something new. Thus, it is really fair to create a new report. Developers hate reports that turn into festivals with continued requests in dozens of comments.
(In reply to Buovjaga from comment #16) ... > No. This is an enhancement report. The commit referenced in comment 6 adds > something new. Thus, it is really fair to create a new report. Developers > hate reports that turn into festivals with continued requests in dozens of > comments. See tdf#131309.
*** Bug 90324 has been marked as a duplicate of this bug. ***