[SOLVED]Conditional formatting on cells using containing "CONCATENATE"

[Laniakea]

Hey there!

I’m having trouble getting conditional formatting to work on cells that link to another which contain the “concatenate” formula.

I have one sheet named “overview” at the beginning of my spreadsheet where I copied the results of the other sheets, so I don’t have to click on each individual sheet to see the data I want.

For example I have two sheets named “Red” & “Blue” that contain a list of the date and amount of balls sold.

The formula for C7 is =IF(SUM($C$3:$C$5)>1;CONCATENATE(SUM($C$3:$C$5);" Balls");CONCATENATE(SUM($C$3:$C$5);" Ball"))

C7 works as I intended, it displays the Sum of C3,C4 & C5 and adds “Balls” if the sum is greater than 1, if not it adds “Ball” to the sum.

I copied/linked that cell with value of C7 into my “overview” sheet where I also copied the C7 value of the “Blue Balls” sheet.

They’re cells C4 & C5 in the overview sheet.

Now I want to conditional format these two cells so that the MAX & MIN value of C4 & C5 are colored red/green but when I try that with “cell value > is equal to > MIN($C$4:$C$5)” it doesn’t change the color of the cells.

I figure that is because C4 & C5 aren’t considered “numbers” by LibreOffice calc and instead are treated as text or something.

Does anybody know how to fix this or work around this?

[mikekaganski]

Of course - you are dealing with strings now after you manipulated the numbers with textual decorations. Don’t do that; instead, use custom number format - like this:

[=1]0" Ball";0" Balls"

[Laniakea]

So C7 should just be reduced to =SUM($C$3:$C$5) and format that cell with [=1]0" Ball";0" Balls" ?

[mikekaganski]

Yes, exactly. And use that format wherever you want to decorate your output like that.

[Laniakea]

Wow how did I not know this feature sooner. It’s so much more elegant.

THANK YOU!!!