Conditional Formatting in OOo Calc

[Mark Foster]

A simple requirement...

A spreadsheet being used as a checklist.

I want conditional formatting:

If the cell contains 'pass' background it green.
If the cell contains 'fail' background it black.
If the cell doesn't contain anything, background it orange.


I can do this in MS Excel in about 3 clicks. OOo can't handle. Have
tried various ways; it can't seem to accurate text-matching, and if I
substitute numeric values (0 for fail, 1 for pass) it sees all blank
cells as 0 (so items not yet checked show up as failures).

Interestingly if I create a .xls in MS Excel and then open it in OOo,
the conditional formatting is preserved - until I click save in OOo.
Then by the time I reopen it in Excel the Conditional formatting is gone.

I'd much prefer to do this in OOo as the users of the spreadsheet will
mainly be Linux clients with OOo installed. Google hits[1] imply that
this is a known issue. and Official support is apparently lacking[2].

Any advice?

Cheers
Mark.

[1] http://openoffice.blogs.com/openoffice/2007/01/conditional_for.html
[2]
http://wiki.services.openoffice.org/wiki/Documentation/FAQ/Calc/Formatting/How_can_I_use_conditional_formatting%3F
shows the howto as 'to be written'.


_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[yuri]

Post by Mark Foster
A spreadsheet being used as a checklist.
If the cell contains 'pass' background it green.
If the cell contains 'fail' background it black.
If the cell doesn't contain anything, background it orange.

OpenOffice is deprecated.
The current suite is now called LibreOffice.

http://help.libreoffice.org/Calc/Applying_Conditional_Formatting

BTW I tried it in OOo 3.2.0 and it works for me.
Remember to put "pass", "fail" and "" inside double quotes in the dialogue box.

HTH
Yuri de Groot

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Mark Foster]

Post by yuri

Post by Mark Foster
A spreadsheet being used as a checklist.
If the cell contains 'pass' background it green.
If the cell contains 'fail' background it black.
If the cell doesn't contain anything, background it orange.

OpenOffice is deprecated.
The current suite is now called LibreOffice.
http://help.libreoffice.org/Calc/Applying_Conditional_Formatting
BTW I tried it in OOo 3.2.0 and it works for me.
Remember to put "pass", "fail" and "" inside double quotes in the dialogue box.

Thanks Yuri.
The Doublequotes appears to be the key.
I can achieve what I need with

Condition 1
Cell value is | equal to | "pass"
then Cell Style GREEN

Condition 2
Cell value is | equal to | "fail"
then Cell Style RED

Condition 3
Cell value is | equal to | ""
then Cell Style UNCHECKED

This is great, a shame you can't keep stacking conditions.

Btw your statement is misleading - OOo is not 'Deprecated'. LibreOffice
is a fork of OpenOffice but OOo appears to be active, as an incubator
project of the Apache Foundation. Aside from that, OOo ships with my
Ubuntu distro (10.10), LibreOffice does not. I expect whether
LibreOffice or OpenOffice are readily available is a distro-level
choice. I'm using what is infront of me, I expect my colleagues who'll
be using this Spreadsheet will use a mix of OOo and LibreOffice
depending on the platform they're using.

Thanks for the pointer.

Cheers
Mark.



_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Tomislav]

Post by Mark Foster
...
Btw your statement is misleading - OOo is not 'Deprecated'. LibreOffice
is a fork of OpenOffice but OOo appears to be active, as an incubator
project of the Apache Foundation.

Well, it's not deprecated _yet_. Seems it's just a matter of time as
most devs went with Libre. Very grand plans for the new project -
android, ios and web versions ... we'll see.

Post by Mark Foster
Aside from that, OOo ships with my
Ubuntu distro (10.10), LibreOffice does not. I expect whether
LibreOffice or OpenOffice are readily available is a distro-level
choice. I'm using what is infront of me, I expect my colleagues who'll
be using this Spreadsheet will use a mix of OOo and LibreOffice
depending on the platform they're using.

Ubuntu comes with Libre since 11.04 so you'll get it when/if you
upgrade. Then again there is that Unity thing in the new releases. :/

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Nevyn]

Post by Tomislav

Post by Mark Foster
...
Btw your statement is misleading - OOo is not 'Deprecated'. LibreOffice
is a fork of OpenOffice but OOo appears to be active, as an incubator
project of the Apache Foundation.

Well, it's not deprecated _yet_. Seems it's just a matter of time as
most devs went with Libre. Very grand plans for the new project -
android, ios and web versions ... we'll see.

By that logic humans, as a race, are dead. It's only a matter of time.This
can be proved by the number of humans who have died in comparison to those
living....

I know some very good people working very hard on OOo.

In the meantime, I'm going to go wait for a computer which makes EVERY other
computer obsolete. It's only a matter of time. :p

Regards,
Nevyn
http://nevsramblings.blogspot.com/
_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Wayne Rooney]

Post by Nevyn
By that logic humans, as a race, are dead. It's only a matter of time.This
can be proved by the number of humans who have died in comparison to those
living....

Nope. Because human population growth is exponential, of all the humans who
have ever lived on this planet, half are alive today.

Wayne

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Don Esslemont]

... Because human population growth is exponential, of all the humans who
have ever lived on this planet, half are alive today.

For another view that the percentage is about 5%) see
http://answers.google.com/answers/threadview?id=217634
--
Don Esslemont
40 Manuka Street, Palmerston North 4401
[phone] or 021 448 769
***@esslemont.org


_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Mark Foster]

Post by Tomislav

Post by Mark Foster
Aside from that, OOo ships with my
Ubuntu distro (10.10), LibreOffice does not. I expect whether
LibreOffice or OpenOffice are readily available is a distro-level
choice. I'm using what is infront of me, I expect my colleagues who'll
be using this Spreadsheet will use a mix of OOo and LibreOffice
depending on the platform they're using.

Ubuntu comes with Libre since 11.04 so you'll get it when/if you
upgrade. Then again there is that Unity thing in the new releases. :/

... and you have the entire reason i've stopped at 10.10 for the time being!



_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Nick Rout]

Post by Mark Foster

Post by Tomislav

Post by Mark Foster
Aside from that, OOo ships with my
Ubuntu distro (10.10), LibreOffice does not.  I expect whether
LibreOffice or OpenOffice are readily available is a distro-level
choice.  I'm using what is infront of me, I expect my colleagues who'll
be using this Spreadsheet will use a mix of OOo and LibreOffice
depending on the platform they're using.

Ubuntu comes with Libre since 11.04 so you'll get it when/if you
upgrade. Then again there is that Unity thing in the new releases. :/

... and you have the entire reason i've stopped at 10.10 for the time being!

If you want ubuntu compatibility and no unity, try mint :)

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Michael Adams]

Post by Tomislav

Post by Mark Foster
...
Btw your statement is misleading - OOo is not 'Deprecated'.
LibreOffice is a fork of OpenOffice but OOo appears to be active,
as an incubator project of the Apache Foundation.

Well, it's not deprecated _yet_. Seems it's just a matter of time
as most devs went with Libre. Very grand plans for the new project
- android, ios and web versions ... we'll see.

I would disagree with you there. Most of the "Core" devs were paid by
Sun before Oracle. Many of them stayed with Oracle during the
libreoffice split (if any chose to become unemployed so they could
contribute to libreoffice i would be extremely surprised). The ones
that left OO.o for TDF were generally contributors to the website,
documentation, languages and other peripheral projects. IBM has put
it's weight behind the Apache incubator project (Rob Weir is very
active there).

Ubuntu and most other Linux distros a;ready contained a fork of OO.o
anyway, which is called Go-OO.

The myth that forks will mean the death of one or other project is not
born out by experience in OSS history.

Having said that: for current community help the best place is the TDF
mailing lists[1]. The Apache-OO.o project will have a significant
period while organisation takes place before any real new development
occurs. The old OO.o mailing lists[2] are still active, just, though
deprecated. Apache has replaced the old users mailing list[3] for
advice and good advice can also be obtained there.

Post by Tomislav

Post by Mark Foster
Aside from that, OOo ships with my
Ubuntu distro (10.10).

That would be Go-OO if you look closely enough (Help - About ...).

[snip]
[1] http://www.libreoffice.org/get-help/mailing-lists/
[2] http://www.openoffice.org/mail_list.html
[3] http://incubator.apache.org/openofficeorg/mailing-lists.html

[Mark Foster]

Post by Michael Adams

Post by Mark Foster
Aside from that, OOo ships with my
Ubuntu distro (10.10).

That would be Go-OO if you look closely enough (Help - About ...).

OpenOffice.org 3.2.1
OOO320m19 (Build:9505)
ooo-build 3.2.1.4, Ubuntu package 1:3.2.1-7ubuntu1.1

Copyright © 2000, 2010 Oracle and/or its affiliates. All rights reserved.
This product was created by Debian and Ubuntu, based on OpenOffice.org.
OpenOffice.org acknowledges all community members, especially those
mentioned at
http://www.openoffice.org/welcome/credits.html.


I assume this is the case because I upgraded from 10.04.



_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Tomislav Skunca]

Post by Michael Adams

Post by Tomislav

...
Btw your statement is misleading - OOo is not  'Deprecated'.
LibreOffice is a fork of OpenOffice but OOo appears to be active,
as an incubator project of the Apache Foundation.

Well, it's not deprecated _yet_. Seems it's just a matter of time
as most devs went with Libre. Very grand plans for the new project
- android, ios and web versions ... we'll see.

I would disagree with you there. Most of the "Core" devs were paid by
Sun before Oracle. Many of them stayed with Oracle during the
libreoffice split (if any chose to become unemployed so they could
contribute to libreoffice i would be extremely surprised). The ones
that left OO.o for TDF were generally contributors to the website,
documentation, languages and other peripheral projects. IBM has put
it's weight behind the Apache incubator project (Rob Weir is very
active there).

That philosophy where devs outside Sun/Oracle are 'contributors',
'enthusiasts' and 'users' doesn't sit well with people. They were
obviously second hand citizens and I completely understand feelings
that come with that. Sun cared a bit about the community but Oracle
doesn't care at all.
But tell me, who will pay the ex-Sun devs to work on the OO.o code
when the IBM-Sun/Oracle deal expires? Oracle certainly wont. I see the
OO.o as a kinda-open project that is mainly IBM's main devel branch of
Lotus. OTOH Libre is a more consumer oriented project that has
different goals (and a different audience).

Post by Michael Adams
Ubuntu and most other Linux distros a;ready contained a fork of OO.o
anyway, which is called Go-OO.

But was it a default office suite? And it's not a fair comparison as
the codebase difference between Libre and OO.o is so much larger.

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[David L Neil]

Mark,

Post by Mark Foster

Post by Mark Foster
A spreadsheet being used as a checklist.
If the cell contains 'pass' background it green.

...

Post by Mark Foster
I can achieve what I need with
Condition 1
Cell value is | equal to | "pass"
then Cell Style GREEN

...

Post by Mark Foster
This is great, a shame you can't keep stacking conditions.

What do you mean by "stacking conditions"?

?condition formatting has a limit of three format choices
?condition being computed, eg pass/fail/null has a result set where n>3
?condition being computed has more than three clauses/more than two AND
or OR conjunctions
?...

There may be a way...

[Mark Foster]

Post by David L Neil

Post by Mark Foster
This is great, a shame you can't keep stacking conditions.

What do you mean by "stacking conditions"?
?condition formatting has a limit of three format choices
?condition being computed, eg pass/fail/null has a result set where n>3
?condition being computed has more than three clauses/more than two
AND or OR conjunctions
?...
There may be a way...

Oh i'm sure there is, but the UI is less than intuitive if that's the case.

Say I want to have different colours for the words 'yes, no, maybe,
other'. That's 4 sets of conditions.


_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[David L Neil]

Mark,

Post by Mark Foster

Post by David L Neil

Post by Mark Foster
This is great, a shame you can't keep stacking conditions.

What do you mean by "stacking conditions"?
?condition formatting has a limit of three format choices
?condition being computed, eg pass/fail/null has a result set where n>3
?condition being computed has more than three clauses/more than two
AND or OR conjunctions
?...
There may be a way...

Oh i'm sure there is, but the UI is less than intuitive if that's the case.

=agreed.

=the limit seems arbitrary and reminds me very much of FORTRAN's trio
(negative, zero, positive). It's sad that the OOo ppl didn't look at the
likes of Thunderbird's message search which visibly ends with an 'add
another clause' (+) button, and thus multiplies the number of conditions
that may be applied. (although presumably there is a limit...)

Post by Mark Foster
Say I want to have different colours for the words 'yes, no, maybe,
other'. That's 4 sets of conditions.

=at the risk of teaching grandma...
(not that I'm calling you an old woman...
dig, dig, dig - hole increasing in depth...)

=I've also been hassled by users demanding this sort of stuff (grumble,
"... users ...", mutter "...OOCalc...", hiss, spit...)

=a solution might be to format the worksheet to have two columns: the
first which color-codes the important results, eg yes/no, and the second
which handles the secondary significance(s).

=further to this, if the coloring is of the font of the information
presented, eg 'pass', 'fail'... (will not work for background or outline
color) it is possible to set the two* adjacent column widths to half of
the width of your widest highlighted-word data, eg 'maybe' is the most
verbose of your examples (above). Of the two, set the left column to be
justified-left, and the right column justified-right. Now, as long as
the field in the 'other column' contains null, the justification will
cause the populated field to expand into/over its empty neighbor. Thus
the two worksheet columns give you 2x3 conditions to play-with but the
results appear to be one (column) to the human eye...

* assumed to be two columns, could relatively easily be three
(justify-center) and thus nine distinct condition-results but would
start to become rather ungainly thereafter...

=such solutions need to look at the UX very carefully! If the user is
likely to print then block-color backgrounds are not printer-friendly.
Maybe consider using (thick) colored outlines?

=Use of color carries inherent (UX) risk in the prevalence of
color-blindness...

=Also, in a long list (regardless of media) where the pertinent data is
some (horizontal) distance from the name (primary data field) - and
particularly if there are intermediate columns which are substantially
blank, it can be difficult for the eye to distinguish between narrow
columns and between the rows...
(?bring back lineflow stationery!)


=I'm assuming that there is a fair volume of information/lines to
present and thus the color-coding is to assist the reader's eye to
pick-out specific exceptions or to gain an impression of a trend. So
another solution may be to become more database-y about the presentation
of information and invite the user to select which category of results
interests him/her and filter accordingly - no color-coding required
because 'this list' of results is all students 'of the same stripe or
color'.

[Tomislav]

Post by Mark Foster
A simple requirement...
A spreadsheet being used as a checklist.
If the cell contains 'pass' background it green.
If the cell contains 'fail' background it black.
If the cell doesn't contain anything, background it orange.
I can do this in MS Excel in about 3 clicks. OOo can't handle. Have
tried various ways; it can't seem to accurate text-matching, and if I
substitute numeric values (0 for fail, 1 for pass) it sees all blank
cells as 0 (so items not yet checked show up as failures).
Interestingly if I create a .xls in MS Excel and then open it in OOo,
the conditional formatting is preserved - until I click save in OOo.
Then by the time I reopen it in Excel the Conditional formatting is gone.
I'd much prefer to do this in OOo as the users of the spreadsheet will
mainly be Linux clients with OOo installed. Google hits[1] imply that
this is a known issue. and Official support is apparently lacking[2].
Any advice?
Cheers
Mark.

Not sure what version you are running but on the latest LibreOffice this
works for me. For matching text fields use double quotes. So:

- select fields
- Format > Conditional Formatting
- Cond 1 ... cell value ... equal to ... "pass"
- create new style and select a green background
- Cond 2 ... cell value ... equal to ... "fail"
- create new style and select a red background
- Cond 3 ... cell value ... equal to ... <leave empty>
- create new style and select an orange background



_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug