Apply icon depending on the text value in the cell

Question

Apply icon depending on the text value in the cell

Navigation

#1 by (3 votes)

2

Assuming the E column, which contains the Sim or the Não value, I'm trying to create a conditional formatting that applies an arrow depending on the text in the cell itself:

Sim   = Verde
Não   = Vermelho  
outro = Amarelo

The issue is that this does not seem to work with text, just with numbers because it already defaults to >= .

If I use numbers, I get the desired result:

Thatresultsin:

But the problem is that -1 , 0 and 1 are not human readable (except for the programmer ...).

Question

In Libre Office Calc , how do you automatically apply an arrow based on a text value in the cell itself? / p>

If impossible, an acceptable alternative is to apply a formatting after conditional formatting where the 1 value is displayed as Sim , and the -1 value as Não . / p>

Spreadsheet example: example.ods

asked by anonymous 07.05.2015 / 14:26

1 answer

My site does not appear in google search engines How to put 2 elements concatenated with variable in a selector with jQuery?

score 3 · Accepted Answer

The problem seems to really be that this type of conditional formatting ( Icon Set ) only works for numeric data types. But I did not find official confirmation for this suspicion anywhere on the Internet (I just found it impossible to use a custom set of icons because the image is compiled next to LibreOffice source code ).

Anyway, I've figured out a way to do what you want with the alternative you suggested yourself: use the actual numeric values in the cell, but display them to the user in the form of text. To do this, simply create new styles specific to the texts "Yes", "No" and "Other".

Open the Styles window by going to the "Format" menu ( Format ) - > "Styles and Formatting" ( Styles and Formatting ) - in my LibreOffice, the shortcut key is F11 .

In this window, right-click on the base style ( Default ) and select new. You will need to create a style for each discrete value of your data (that is, one for "Yes", one for "No" and one for "Others" - as in your example). Name your style on the main tab.

In the "Numbers" tab, select the user-defined format (in my case English) in the category and type the string that you want it to appear (in quotation marks!) in the format code field ( Format code ). Click Ok and this style will be saved.

Do the same for all styles. At the end you'll get something like this:

Now go to the data column you want to format (which, of course, should contain numeric values 1, 0 or -1 instead of text) and usually add the conditional formatting of icons (Icon Set) that you already have was using. Then click "Add" to add a new simple formatting, choosing the value and style to apply.

Repeat for all possible values / styles (in your example, at the end you will have 4 simultaneous conditions: the icons, and 1 for each possible text "Yes", "No" and "Other"). Once applied, your data will be displayed as you wish, even though the value of the cells is still numeric (as you can tell by the number displayed above, where indicated in red).