Description: Generally in tools, whether it's a graphical or textual interface, it's good to have some prompts telling us what we can do with a tool and explaining how we can do it. The basis of this report is that, using a spreadsheet, I wanted to highlight some cell in green (emphasis: "good") by quickly typing the + sign and pressing enter. Steps to Reproduce: And for this solution I opened the function: Format --> Conditional --> Condition.... and here we are. I wanted to set a condition making: "when I type + then the cell will turn green". So I set: Condition 1 Cell value > is equal to > +. simply a solitary plus. Actual Results: And this condition did not work. I spent several minutes trying to use other conditions "is not equal", "contains", "does not contain". Then I tried to enter some number, e.g. 1, and the cell finally turned green. So I went back to some word e.g. abc - and still nothing came out. But fortunately this time I got a hint in this condition maker. At the bottom of the section it displayed a shy and faintly noticeable message: "Strings without quotes are interpreted as column/row labels". Expected Results: And it was only then that I understood: I should type: "+" (plus in quotes). Reproducible: Always User Profile Reset: No Additional Info: The conclusion of these struggles is that if some user does not think to try to type different types of characters (letters) then he will not be able to set the correct condition in his formatting. Such situations cause us to complete simple tasks only after a long time. Sometimes in a few minutes, sometimes after searching the Internet or sometimes even after a few days with a fresh mind. If the tool interface had prompts clearly visible and easily accessible for checking in the area of the window where we work, telling what we can do with the tool and explaining how we can do it, this simple task in 99% of cases would be completed by the user in 1-2 minutes. What I suggest is either to add a case where we type in the field: +, -, %, (, ) and other characters that are part of formulas with a hint: Eg. "This is a sign used in formulas. Fininsh your formula or write this text in quotes". Or: "To treat as text write in quotes". Or maybe prepare a small cheat sheet that pops up when you hover over a field or click on an icon in that field (like in GNOME Files or FreeCommander: attached pictures 8 and 9).
Created attachment 181603 [details] Hint to enter a value.
Created attachment 181604 [details] 2 enter a value with exclamation
Created attachment 181605 [details] 3 Conditional Formatting (leters) + warning triangle
Created attachment 181606 [details] 4 Conditional Formatting (text in quotes).png
Created attachment 181607 [details] 5 Conditional Formatting - is equal to 1 (number)
Created attachment 181608 [details] 6 Conditional Formatting - is equal to plus + (characters from formulas)
Created attachment 181609 [details] 7 eg smb connection in GNOME
Created attachment 181610 [details] 8 description after clicking on the question mark (GNOME)
Created attachment 181611 [details] 9 examples of quick filter on hover (FreeCommander)
Created attachment 181612 [details] 10 Conditional Formatting - equals sign
I agree this is a terrible situation, and I see some variants here: 1. Don't touch the attention phrase "Strings without quotes are interpreted as column/row labels" but add all symbols except numbers to condition for it displaying into source code 2. In addition to p.1 to highlight the label "Strings without quotes are interpreted as column/row labels" with yellow color for example 3. Delete the label "Strings without quotes are interpreted as column/row labels" and create some tooltip for field as Piotr suggest UX-team, here is a task for you =)
First of all I wonder how the plus sign is being interpreted since other alphanumeric characters show the static tip. So we could improve the situation by accepting the + sign. However, since curly brackets tell me "invalid value" unless it has a closing pendant I suspect it's possible to do some magic here. => needsDevAdvice What also comes in mind is to have a special condition for the column labels. Currently it's a "cell value" and it could be "column label" (and we take everything which is not a number as string). And last but not least I think the preview should give some feedback on the proper input. As discussed in bug 150448 we want to replace the list value of values by the static label "This is a preview" and it could be "No valid input" if the entered value is unclear.
The message "Strings without quotes are interpreted as column/row labels" is nonsense anyway. Strings without quotes are interpreted as whatever the formula expression context evaluates them to, that *may* be column/row labels, or cell references or named ranges or named expressions or database range names, ... i.e. the warning goes away if input is completed to such valid expression. The important part is that if one wants to compare against a literal string then one has to enclose the text in double quotes, as in any formula expression. Entering a single + character is just the same as entering =+ as cell formula (which if closed evaluates to Err:520 syntactical error). An additional check probably could test the compilation result of the formula expression, but you don't want that for every key pressed.. Furthermore there are errors that are detected only by the interpreter and can be even position dependent, which comes into play if the conditional format is copied around. There's not much that can be done there except interpreting the formula expression once *on the position where it is defined* when entering it.
I believe an input field tells the user that it simply accepts text. Any restriction to numbers, dates etc. should have an indicator, if the input is processed it needs some kind of feedback like good/bad. But how should anyone know that entering an equation is possible and the result is being used? And for what purpose is that complexity good for? Anyway, we discussed the topic in the design meeting and follow Eike's suggestion to resolve WF. We have top accept corner cases.
Created attachment 182150 [details] 12 lack of exiting quotation mark
Created attachment 182151 [details] 13 lack of opening quotation mark
The rules of highlighting shown in photos 12 and 13 look like an inconsistency.
Created attachment 182152 [details] 15 proposition of hints Suggestions in the sketch in image 15: 1 * Field names on mouseover would be the easiest improvement. Although it does not bring much to the topic. 2 * A cheat sheet activated with a button next to the condition value field would help users in most cases. 3 * Formula validation could be a very helpful function to enter code. And this function could be adapted here as a general tool for displaying hints and checking correctness of typing a formula. What would be appreciated when typing formulas into cells in a spreadsheet.
Created attachment 182153 [details] 16 formula
(In reply to Eike Rathke from comment #13) > An additional check probably could test the compilation result of the > formula expression, but you don't want that for every key pressed.. We actually do that already, good; that probably should use the non-permissive more errors catching (instead of leaving that to the interpreter later) mode though and fine tune result conditions.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/071fc970cd1deb326a30b667bd26c843890d97ad Related: tdf#150271 Adjust unquoted string message to reality It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Heiko Tietze from comment #14) > I believe an input field tells the user that it simply accepts text. Any > restriction to numbers, dates etc. should have an indicator, if the input is > processed it needs some kind of feedback like good/bad. But how should > anyone know that entering an equation is possible and the result is being > used? And for what purpose is that complexity good for? ? There are enough cases in the wild where a condition evaluates against a formula expression, and if it's only to compare against the calculation of some neighbouring cells. > Anyway, we discussed the topic in the design meeting and follow Eike's > suggestion to resolve WF. I did not suggest to resolve this as WF. I'm implementing a better check on the input expression that will clarify error conditions, without modifying the UI any further.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/c23c209a063273fda0670203bfbe3bf8ed6eb249 Resolves: tdf#150271 Further error checks of conditional formatting expression It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Fix verified in: Version: 7.5.0.1 (X86_64) / LibreOffice Community Build ID: 77cd3d7ad4445740a0c6cf977992dafd8ebad8df CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Issue was already in OOo 3.3. Thanks everyone!