Add an option "Case Sensitive" to tab Criteria so that the input value can be checked in case sensitive. For example, a list of allowable values: ZY0199 GD0200 If "Case Sensitive" is checked, input value zy0199 is invalid. Background of this report: in current implementation, value zy0199 is an valid input and this value remains in the cell, but what I want is the cell contains value ZY0199. Then, a solution I can think of is either to change the value to the matched one or just do a case sensitive match. The latter one is preferred from my perspective, that's what I would like to propose. Thank you.
IMO, the data validity must follow the general Options->Calc->Calculate->Case sensitive. That it doesn't is a bug IMO, but otherwise, no additional controls are needed. FTR: If the validity rule is e.g. List, with the sole entry aBc, then Excel disallows abc or ABC, but allows aBc; while Calc allows all of these (irrespective of the case sensitivity global setting). If needed that a specific validity rule case sensitivity was different from the global setting, the validity may use formulas, which may be made case sensitive e.g. using REGEX with its (?i) flag.
(In reply to Mike Kaganski from comment #1) > IMO, the data validity must follow the general > Options->Calc->Calculate->Case sensitive. That it doesn't is a bug IMO, but > otherwise, no additional controls are needed. The help message of "Options->Calc->Calculate->Case sensitive" confuses me. It says "Disable case sensitivity for interoperability with Microsoft Excel". It is clear that it is for interoperability with MS Excel, but it's not clear for me which LO features this option applies to. If the data validity follows that option, a further case might be a user don't want the case sensitive for the interoperability with MS Excel, then uncheck that option, then the data validity behavior is changed which may not be expected by the user. Should that help message be updated to be more specific? Or, an additional case sensitive option is required for LO features that is not related to the interoperability with MS Excel. BTW, I'm not familiar with the background, implementation and any consideration about interoperability with MS Excel. So, my thought about the case sensitive may be wrong. Please point out to me if my understanding is not correct. And any workaround is also acceptable for me. :) > FTR: If the validity rule is e.g. List, with the sole entry aBc, then Excel > disallows abc or ABC, but allows aBc; while Calc allows all of these > (irrespective of the case sensitivity global setting). > > If needed that a specific validity rule case sensitivity was different from > the global setting, the validity may use formulas, which may be made case > sensitive e.g. using REGEX with its (?i) flag. One of my cases is using a named cell range to set the data source. How do I use formulas alternatively? The named cell range can increase and reduce dynamically by adding or removing rows from a specific sheet.
The functions and operators affected by case-sensitivity are defined in the ODF OpenFormula standard (ODFF), v1.3 has 3.4 Host-Defined Behaviors, https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017868_715980110 1.HOST-CASE-SENSITIVE: if true, text comparisons are case-sensitive. This influences the operators =, <>, <, <=, >, and >=, as well as database query functions that use them. Note that the EXACT function is always case-sensitive, regardless of this calculation setting. The database query functions are the D*() functions (DAVERAGE(), DCOUNT(), ...). Note that UI behaviour like Data Validity during input is not defined in the standard (and shouldn't). If it makes sense we can let it follow the option or implement even something different. If case-sensitivity is enabled it makes sense to allow only strictly matching input, but if Excel restricts validity also for case-insensitivity we may need something else. (In reply to qcxhome from comment #2) > One of my cases is using a named cell range to set the data source. How do I > use formulas alternatively? The named cell range can increase and reduce > dynamically by adding or removing rows from a specific sheet. Use Validity Criteria, Allow: Either Custom, and in Formula enter a formula that evaluates to either TRUE (<>0, good input) or FALSE (=0, bad input). Or Cell range, and in Source enter a formula that returns an area or array. References used in such formulas are adjusted the same as in other formula expressions and named ranges and expressions. See also https://help.libreoffice.org/7.6/en-GB/text/scalc/01/12120100.html?&DbPAR=CALC&System=UNIX For further questions about details and possible solutions please use https://ask.libreoffice.org/ and not this bug tracker; you may link back the question here so others can follow, thanks.
Thank you all for your input. So "Tools > Options > Calc > Calculate > Case sensitive" is a compatibility setting for formulas' comparison operators, see also corresponding help: https://help.libreoffice.org/24.2/en-US/text/shared/optionen/01060500.html#bm_id3151205 ... so I understand we shouldn't mix "pure GUI" string data validity in it. Workaround in Comment 1 is useful, but I think the feature suggested here would be welcome: an extra setting in the Validity dialog (which I think should be limited to the Cell Range and List options). However, I want to hear the UX/Design team's opinion as well. --- As a side note, I've noticed a slight UI discrepancy (at least with the gtk3 VCL plugin): The lowercase version is accepted when entered, but when clicking the dropdown to see available values, the uppercase version listed in it is not highlighted (which might give the impression to users that it shouldn't be accepted).
Created attachment 190374 [details] test ODS
(In reply to Stéphane Guillou (stragu) from comment #5) > Created attachment 190374 [details] > test ODS (probably should deactivate AutoInput to test)
If the validation list contains "yes, yES" we also accept "Yes" while Excel pops-up the validation list case insensitively and auto-selects the first item. Usually I'm all in for more flexibility but in this case the task is to take exactly one item out of many defined in the validation list. I believe users wont be happy if "Yes" would be rejected neither binding the behavior to case sensitivity in formulas makes sense to me. I suggest to pop-up the dropdown and filter the list respectively. The actual validation happens while typing in this case, being case insensitive.
(In reply to Heiko Tietze from comment #7) > I believe users wont be happy if "Yes" would be rejected So what is your recommendation? Add the option in the dialog for cases where case-sensitivity matters, but keep case-insensitive as the default? That would be my preferred solution. > neither binding the > behavior to case sensitivity in formulas makes sense to me. Agreed, if are talking about the setting in the Calculate dialog. > I suggest to pop-up the dropdown and filter the list respectively. The > actual validation happens while typing in this case, being case insensitive. I'm not sure I understand this. Do you mean that the list should pop-up when entering edit mode, reduce in size showing only the matches while typing, and highlight the case-insensitive match? I assume this is a reply to my "side note" and should be a separate enhancement request.
(In reply to Stéphane Guillou (stragu) from comment #8) > > I suggest to pop-up the dropdown and filter the list respectively. The > > actual validation happens while typing in this case, being case insensitive. > > I'm not sure I understand this. Rather than accepting every input we should list what matches the entered characters. If "y" is typed, the validation list respectively its dropdown should pop-up and show all items that start with "y". If none matches the dropdown is empty. The difference to the current solution is that we check 'onkeydown' rather than 'onleave/enter'. The validation goes into the dropdown management and therefore the UI. By doping so it is not possible to enter "Yes" while only lower-case keywords are in the validation. I disagree with a checkbox to accept anyway (and kind of extend the list) or to reject the case sensitive keyword. And the proposed solution is to convert on input (it would be acceptable if this is done afterwards, onleave/enter).