Download it now!
Bug 42282 - Conditional formatting with text cell-values needs documentation
Summary: Conditional formatting with text cell-values needs documentation
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: HelpGaps-NewFeatures Conditional-Formatting
  Show dependency treegraph
 
Reported: 2011-10-26 08:43 UTC by Allan Jacobs
Modified: 2017-04-08 18:49 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Conditional formatting example using numbers, text, and currency (11.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-10-26 08:43 UTC, Allan Jacobs
Details
Sample example of how to use text contents of a cell for conditional formatting. (12.81 KB, image/png)
2015-05-08 17:51 UTC, Charles Curley
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Allan Jacobs 2011-10-26 08:43:31 UTC
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.
Comment 1 sasha.libreoffice 2012-04-18 08:15:44 UTC
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
Comment 2 Patrick Oltmann 2012-05-13 09:52:45 UTC
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?
Comment 3 sasha.libreoffice 2012-05-14 02:13:34 UTC
Thanks for additional testing
If both changes in one Help page, then place them in this bugreport
Comment 4 Patrick Oltmann 2012-05-14 15:21:29 UTC
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
Comment 5 sasha.libreoffice 2012-05-15 02:58:10 UTC
Thanks for help in improving Libre Help
Comment 6 sasha.libreoffice 2012-05-15 03:00:32 UTC
@ David
What do You think about this bug?
Comment 7 Maciej Rumianowski 2012-11-29 09:13:58 UTC
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)
Comment 8 Charles Curley 2015-05-08 17:49:40 UTC
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.
Comment 9 Charles Curley 2015-05-08 17:51:47 UTC
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.
Comment 10 Markus Mohrhard 2016-09-19 17:39:32 UTC
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.
Comment 11 Charles Curley 2016-09-27 17:26:55 UTC
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).
Comment 12 Markus Mohrhard 2017-04-08 18:49:55 UTC
(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.