[Solved] Conditional format strangeness

Discuss the spreadsheet application

[Solved] Conditional format strangeness

Postby arathra » Wed May 01, 2019 1:59 pm

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
arathra
 
Posts: 13
Joined: Sun Jul 12, 2015 6:49 pm

Re: conditional format strangeness

Postby Zizi64 » Wed May 01, 2019 2:31 pm

Please upload a real, ODF type sample file here.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8145
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: conditional format strangeness

Postby RoryOF » Wed May 01, 2019 3:02 pm

Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29285
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: conditional format strangeness

Postby gerard24 » Wed May 01, 2019 3:21 pm

Just put double quotes around text : "Joe"
LibreOffice 6.2.4 on Windows 10
gerard24
Volunteer
 
Posts: 948
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: conditional format strangeness

Postby arathra » Thu May 02, 2019 9:06 am

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
arathra
 
Posts: 13
Joined: Sun Jul 12, 2015 6:49 pm

Re: conditional format strangeness

Postby keme » Thu May 02, 2019 9:21 am

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
keme
Volunteer
 
Posts: 3210
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Conditional format strangeness

Postby arathra » Thu May 02, 2019 1:57 pm

Strangeness eliminated indeed! Thanks!
OpenOffice 4.1.0 on Windows 7
User avatar
arathra
 
Posts: 13
Joined: Sun Jul 12, 2015 6:49 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 22 guests