Created attachment 52801 [details] Conditional formatting example using numbers, text, and currency In the Ubuntu forum yesterday, someone asked whether it was possible to apply a conditional format to cells that have text values instead of numerical values. All of the old OpenOffice documentation, LibreOffice on-line documentation, and local Help files contain examples that use numeric values. It turns out that it is possible to use cell values in conditional formatting if the values are enclosed in double quotation marks. This should be documented whenever and wherever conditional formatting is documented.
Thanks for bugreport Please, copy-paste to here paragraph from English Help, that should be changed. Then copy-paste it again and change how it should be. Thanks in advance
The problem still exists in LibO 3.5.3.2. I just wanted to provide an updated version of the help page, but then I realizied that the help page still mentions the restriction to have a maximum of three conditions (which was lifted in 3.5). Should this be filed as an additional bug or should I just post the updated help page containing the changes for both issues?
Thanks for additional testing If both changes in one Help page, then place them in this bugreport
Yes, it's both on the same page ("Conditional Formatting") that is found via the search terms "conditional formatting;conditions" in the Calc online help. === Original Help Page (LibO 3.5.3.2) === Choose Conditional Formatting to define format styles depending on certain conditions. If a style was already assigned to a cell, it remains unchanged. The style entered here is then evaluated. You can enter three conditions that query the contents of cell values or formulas. The conditions are evaluated from 1 to 3. If the condition 1 matches the condition, the defined style will be used. Otherwise, condition 2 is evaluated, and its defined style used. If this style does not match, condition 3 is evaluated. To access this command... Choose Format - Conditional Formatting To apply conditional formatting, AutoCalculate must be enabled. Choose Tools - Cell Contents - AutoCalculate (you see a check mark next to the command when AutoCalculate is enabled). Condition 1/2/3 Mark the boxes corresponding to each condition and enter the corresponding condition. To close the dialog, click OK. Cell Value / Formula Specifies if conditional formatting is dependent on a cell value or a formula. If you select a formula as a reference, the Cell Value Condition box is displayed to the right of the Cell value/Formula field. If the condition is "Formula is", enter a cell reference. If the cell reference is a value other than zero, the condition matches. Cell Value Condition Choose a condition for the format to be applied to the selected cells. Cell Style Choose the style to be applied if the specified condition matches. New Style If you haven't already defined a style to be used, you can click New Style to open the Organizer tab page of the Cell Style dialog. Define a new style there and click OK. Parameter field Enter a reference, value or formula in the parameter field, or in both parameter fields if you have selected a condition that requires two parameters. You can also enter formulas containing relative references. Once the parameters have been defined, the condition is complete. It may appear as: Cell value is equal 0: Cell style Null value (You must have already defined a cell style with this name before assigning it to a condition). Cell value is between $B$20 and $B$21: Cell style Result (The corresponding value limits must already exist in cells B20 and B21). Formula is SUM($A$1:$A$5)=10: Cell style Result (The selected cells are formatted with the Result style if the sum of the contents in cells A1 to A5 is equal to 10). Shrink / Maximize Click the Shrink icon to reduce the dialog to the size of the input field. It is then easier to mark the required reference in the sheet. The icons then automatically convert to the Maximize icon. Click it to restore the dialog to its original size. The dialog is automatically minimized when you click into a sheet with the mouse. As soon as you release the mouse button, the dialog is restored and the reference range defined with the mouse is highlighted in the document by a blue frame. Shrink Maximize Related Topics Applying Conditional Formatting === Modified Help Page === Choose Conditional Formatting to define format styles depending on certain conditions. If a style was already assigned to a cell, it remains unchanged. The style entered here is then evaluated. You can enter an unlimited number of conditions that query the contents of cell values or formulas. The conditions are evaluated according their order on the list. If a condition matches the cell content, the defined style will be used and processing be stopped. Note that in this case subsequent conditions will not be evaluated, thus more specific conditions should be given priority (i.e. be higher up on the list) over less specific ones. To access this command... Choose Format - Conditional Formatting To apply conditional formatting, AutoCalculate must be enabled. Choose Tools - Cell Contents - AutoCalculate (you see a check mark next to the command when AutoCalculate is enabled). Condition Mark the boxes corresponding to each condition and enter the corresponding condition. To close the dialog, click OK. Cell Value / Formula Specifies if conditional formatting is dependent on a cell value or a formula. If you select "Cell Value is" as a reference, the Cell Value Condition box will be displayed to the right of this field. If the condition is "Formula is", enter a cell reference. If the cell reference is a value other than zero, the condition matches. Cell Value Condition Choose the condition that will be used to compare the cell value versus the value in the parameter field. Parameter field Enter a reference, value or formula in the parameter field, or in both parameter fields if you have selected a condition that requires two parameters. You can also enter formulas containing relative references. When text is used for comparisons, it has to be enclosed in double quotes. Once the parameters have been defined, the condition is complete. It may appear as: Cell value is equal 0: Cell style Null value (You must have already defined a cell style with this name before assigning it to a condition). Cell value is between $B$20 and $B$21: Cell style Result (The corresponding value limits must already exist in cells B20 and B21). Formula is SUM($A$1:$A$5)=10: Cell style Result (The selected cells are formatted with the Result style if the sum of the contents in cells A1 to A5 is equal to 10). Shrink / Maximize Click the Shrink icon to reduce the dialog to the size of the input field. It is then easier to mark the required reference in the sheet. The icons then automatically convert to the Maximize icon. Click it to restore the dialog to its original size. The dialog is automatically minimized when you click into a sheet with the mouse. As soon as you release the mouse button, the dialog is restored and the reference range defined with the mouse is highlighted in the document by a blue frame. Shrink Maximize Cell Style Choose the style to be applied if the specified condition matches. New Style If you haven't already defined a style to be used, you can click New Style to open the Organizer tab page of the Cell Style dialog. Define a new style there and click OK. Related Topics Applying Conditional Formatting
Thanks for help in improving Libre Help
@ David What do You think about this bug?
What is the status of this bug? Current help page still doesn't mention double quotation marks. (http://opengrok.libreoffice.org/xref/help/source/text/scalc/01/05120000.xhp)
This bug has been open for more than three years. It is still present in 4.4.3.2. Fixing this would have saved me almost a day of working out how to use text contents of a cell. See the attached example, conditional.formatting.text.comparison.double.quotes.png, taken from my application.
Created attachment 115455 [details] Sample example of how to use text contents of a cell for conditional formatting. Sample example of how to use text contents of a cell for conditional formatting.
I thought we basically fixed that at some point with the warning that unquoted strings are interpreted as column/row labels. At least current versions (most likely all 5.x versions, at least 5.1+) have a warning.
Thanks. Where is that warning? I do not see it in the Help entry "conditional formatting;cells", where I expect to see it. LibreOffice Version: 5.1.5.2 Build ID: 1:5.1.5~rc2-1~bpo8+1 on Debian GNU/Linux 8.5 (jessie).
(In reply to Charles Curley from comment #11) > Thanks. > > Where is that warning? I do not see it in the Help entry "conditional > formatting;cells", where I expect to see it. > > LibreOffice Version: 5.1.5.2 Build ID: 1:5.1.5~rc2-1~bpo8+1 on Debian > GNU/Linux 8.5 (jessie). You find it in the dialog when you add a conditional format. Add something like test and it will warn you that this entry will be interpreted as column/row label and not as a string.