We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2019-02-27 15:53:32 +0200

Laniakea gravatar image

updated 2020-08-09 20:01:31 +0200

Alex Kemp gravatar image

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.

image description

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. image description

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-02-27 16:05:06 +0200

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"
edit flag offensive delete link more


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

Laniakea gravatar imageLaniakea ( 2019-02-27 16:20:10 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-02-27 16:22:50 +0200 )edit

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


Laniakea gravatar imageLaniakea ( 2019-02-27 16:29:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-27 15:53:32 +0200

Seen: 1,140 times

Last updated: Feb 27 '19