[Solved] Conditional format strangeness

Discuss the spreadsheet application
Post Reply
User avatar
arathra
Posts: 13
Joined: Sun Jul 12, 2015 6:49 pm

[Solved] Conditional format strangeness

Post by arathra »

I'm having a strange result in my conditional formatting.
I selected a COLUMN where the conditional formatting is needed.
I clicked on FORMAT > CONDITIONAL FORMATTING and in the dialog box put in > If the cell = 'JOE' then use style GREENBACKGROUND.
But then when I click OK, the entire column turns to green EXCEPT for ANY cell which is NOT empty.
Strange and not what I expected. Obviously I'm doing something the wrong way around - can anyone help?
Thanks!
Attachments
Capture.JPG
Last edited by Hagar Delest on Thu May 02, 2019 11:01 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.0 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: conditional format strangeness

Post by Zizi64 »

Please upload a real, ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: conditional format strangeness

Post by RoryOF »

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: conditional format strangeness

Post by gerard24 »

Just put double quotes around text : "Joe"
LibreOffice 6.4.5 on Windows 10
User avatar
arathra
Posts: 13
Joined: Sun Jul 12, 2015 6:49 pm

Re: conditional format strangeness

Post by arathra »

gerard24 wrote:Just put double quotes around text : "Joe"
Perfect... and so simple when you know how! Thanks! :)
OpenOffice 4.1.0 on Windows 7
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: conditional format strangeness

Post by keme »

gerard24 wrote:Just put double quotes around text : "Joe"
Here is why it works:
  • When you test for equality to 'Joe' (or equality to Joe, i.e. unquoted), Calc takes it as a "data name" (cell, range, variable, etc.). Single quotes is only required when your data name contains spaces, but will always "dereference" the quoted content. If a name is undefined, the value returned is numerical zero. Numeric return from an empty cell is also zero. Hence, 'Joe' equals empty cells and the style is applied.
  • When you test for equality to "Joe" (using double quotes), calc will take it as actual data and compare character by character. "Joe" does not equal an empty cell.
Strangeness eliminated, hopefully.
User avatar
arathra
Posts: 13
Joined: Sun Jul 12, 2015 6:49 pm

Re: Conditional format strangeness

Post by arathra »

Strangeness eliminated indeed! Thanks!
OpenOffice 4.1.0 on Windows 7
Post Reply