[Solved] Style is not changing with conditional OR function

Discuss the spreadsheet application
Post Reply
Bald Eagle
Posts: 68
Joined: Wed Apr 19, 2017 9:22 pm

[Solved] Style is not changing with conditional OR function

Post by Bald Eagle »

I currently have:

=IF(COUNTIF(A$8:A$4350;A8)>1;"THERE ARE "&COUNTIF(A$8:A$4350;A8)-1&" DUPLICATES OF THIS MODEL NUMBER***";"Unique Entry") & T(STYLE(IF(ISBLANK($P8);"Default";IF($P8="Error";"RedStyle";IF($P8="Note";"YellowStyle";IF(LEFT($P8;8)="Verified";"GreenStyle";"Default"))))))

in a column, and this seems to work fine.

I'd also like to have the RedStyle applied when COUNTIF(A$8:A$4350;A8)>1

I tried using:
...IF(OR($P8="Error";COUNTIF(A$8:A$4350;A8)>1);....

But even though I independently test
=OR($P8="Error";COUNTIF(A$8:A$4350;A8)>1)
in a separate cell, and it returns TRUE, the cell does not have RedStyle applied to it. Entering "Error" into cell P8 still works though.
I even tried switching the order:
=OR(COUNTIF(A$8:A$4350;A8)>1;$P8="Error")
Same result - the cell does not have RedStyle applied to it but entering "Error" into cell P8 still works.

I'm sure I'm just doing something wrong that I just can't see.

(I already know that this sort of thing is a list and ought to be done in a database, not in Calc --- but that's what I have to work with at the moment.)

Any help would be greatly appreciated. :)
Last edited by RoryOF on Tue Nov 21, 2017 11:37 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
OpenOffice 4.1.1 on Windows 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Style is not changing with conditional OR function

Post by RusselB »

Untested, but what if you tried your second comparison (whichever one you pick) and enter it as a second Conditional Format, rather than having the two comparisons in the one CF item?
Working from my phone right now, so I don't have access to Calc to test.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Bald Eagle
Posts: 68
Joined: Wed Apr 19, 2017 9:22 pm

Re: Style is not changing with conditional OR function

Post by Bald Eagle »

Hi Russel,
Thanks for the idea (I often contribute to other forums where I have no access to the software to test code at work).
I'm using styles applied with a formula, not drop-down conditional formatting.

And just as an addendum: It would be great if there was a way to better keep track of nested parenthetical statements - color coding, bold highlighting, mixed types - ( [ {, or some form of indentation...
OpenOffice 4.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Style is not changing with conditional OR function

Post by Villeroy »

Code: Select all

=IF(COUNTIF(A$8:A$4350;A8)>1;"DUP";"UNIQ") & T(STYLE(IF(OR($P8="Error";COUNTIF(A$8:A$4350;A8)>1);"RedStyle";IF($P8="Note";"YellowStyle";IF(LEFT($P8;8)="Verified";"GreenStyle";"Default")))))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Bald Eagle
Posts: 68
Joined: Wed Apr 19, 2017 9:22 pm

Re: Style is not changing with conditional OR function

Post by Bald Eagle »

Well Villeroy, Stick another feather in your cap :)
I'm not sure what the issue was - but you got it to work. Was it a wrong pairing of parentheses?
Thank you ever so much for fixing what I'm sure was a trivial problem. :)
OpenOffice 4.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Style is not changing with conditional OR funct

Post by Villeroy »

I don't know what it was. I just added the OR(this;that) and I removed the additional "Default" case because it is already set to "Default" in any non-matching case.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply