How should I apply conditional formatting to achieve this?

Discuss the spreadsheet application

How should I apply conditional formatting to achieve this?

Postby neiloneil » Tue Oct 02, 2018 10:46 am

In the attached sample spreadsheet, I would like to assign a unique colour to the rows corresponding to the customer reference in column C.
When I type " alms" into cell C6, I wish the row to display as colour green 9. as in rows 2 to 4.
How should I apply conditional formatting to achieve this please?
Attachments
Sample spreadsheet .ods
(12.92 KiB) Downloaded 37 times
open office 4.1 on windows 10, 64 bit
neiloneil
 
Posts: 8
Joined: Fri Apr 27, 2018 1:27 pm

Re: How should I apply conditional formatting to achieve thi

Postby Zizi64 » Tue Oct 02, 2018 12:31 pm

From the sample file
In the sample spreadsheet, I would like to assign a unique colour to the rows corresponding to the customer reference in column C.
When I type alms into cell C6, I wish the row to display as colour green 9.
How should I apply conditional formatting to achieve this please?

Please read the HELP description of the Conditional Format function.

Note:
The Conditional Format function of the Apache OpenOffice Calc can handle 3 conditions only.
How many different customer you want handle?

The Conditional Format function of the LibreOffice Calc can handle MORE THAN 3 conditions.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.3.2; 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: 8460
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How should I apply conditional formatting to achieve thi

Postby RoryOF » Tue Oct 02, 2018 12:35 pm

There is an extension to OO that permits more conditional formatting
Extend your conditional formatting
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29738
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How should I apply conditional formatting to achieve thi

Postby Villeroy » Tue Oct 02, 2018 1:46 pm

It is a draw back of the styles concept that you need one style for each key code and for each number format in this particular case.
That makes 2x3 cell styles.
First I created the 6 cell styles and named them after the key ("alms" or "ics") and the number format ("General", "Date" or "Currency").
I made a style table on Sheet2 and named the range "Styles".
Then I removed all formatting from your list and added 3 different conditional formats to A2:A6 for the date styles, B2:J6;O2:P6 for the general styles and K2:N6 for the currencies.
---------------
The number format condition makes use of the STYLE function, so you only need one condition per cell range.
For the general number format in row 2 it is:
Code: Select all   Expand viewCollapse view
STYLE(VLOOKUP($C2;Styles;2;0))

which looks up the key in this row's column C in the first column of the named Styles list (Sheet2.A2:D3) and returns the style name from column #2.
For the date styles the column index is 3 and for the currencies it is column index 4.
For more colour styles you have to expand the named list Styles accordingly.
--------------
On your side you need to be familiar with:
1) OpenOffice's style concept
2) Absolute, relative and mixed references in all spreadsheet programs
3) Named cell ranges
4) How to expand lists by insertion of rows so all references to these lists and the conditional formatting update properly. In this particular case both lists on Sheet1 and Sheet2.
Attachments
conditional_format_t95308.ods
(17.53 KiB) Downloaded 27 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27287
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How should I apply conditional formatting to achieve thi

Postby Lupp » Tue Oct 02, 2018 2:28 pm

I'm lost now. Obviously I neither understood the question nor the replies.

When I came to the thread, I just had typed a question and a remark in return:
myself wrote:Do you (the OriginalQuestioner) mean the content of cell $C$2 to be the "customer reference" you were talking of?
If so it's simple: Define a cell style based on the 'Default' style changing nothing but the background colour to the desired value (the colour names may change over time). Then select the cells you want to conditionally format, select the 'Formula is' mode and...

Now I hesitated because relative addresses used in CF are a bit ticklish to describe for AOO: What's needed depends on the way the range was selected. As I tend in the given case to first select A2:P2 left to right, and then to expand the selection down to the bottom row of the sheet, the selection would be A2:P1048576 with P1048576 being the base cell for relative addresses. Thus my condition would have to be $C1048576=$C$2. (And the conditionally overlaid cell style with green background might be named "csMatchCustomerReference" or whatever.)

Then I was distracted, and when I came back my idea of understanding the question basically vanished. In specific I did not understand for what reason more than three conditions might be needed, and by what the need for different conditional cell styles regarding number formats should be caused. Conditional styles are not assigned but overlaid for the view. Attributes that were not changed when the style was defined will not be touched when the overlay is done. This is fundamentally different from the way a style is applied if the STYLE() function is used.

Would someone please help me to understand?
To help you with this, I attach the "solution" I was about to sketch when I was interrupted.
Attachments
a0095308specialCF_1.ods
(37.03 KiB) Downloaded 23 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How should I apply conditional formatting to achieve thi

Postby Villeroy » Tue Oct 02, 2018 3:54 pm

I was assuming that the real data have more than 2 different customers in column C.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27287
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How should I apply conditional formatting to achieve thi

Postby Lupp » Tue Oct 02, 2018 4:51 pm

Villeroy wrote:I was assuming that the real data have more than 2 different customers in column C.
Yes. That seems plausible, but as the OQ only talked of one CF naming one BackColor expressly, I regarded it concludent that only a specific one of the probably very many customer keys should rule the CF. The only sufficiently distinct customer key for the role was the first one. In addition I could (and can) not imagine the sheer intention to mark an arbitrary number of keys with a different colour each. Irritating, however, was the unmentioned but striking colouring of the "ics" row.

Well, I'll let the topic sleep now.
Thanks to Villeroy for the answer.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How should I apply conditional formatting to achieve thi

Postby neiloneil » Tue Oct 02, 2018 7:11 pm

Thanks to those answering my question, and supplying formatted sheets exactly as I asked .
I had hoped to work out the method from those sheets, but am still struggling a bit.
I have a much larger spreadsheet with several customer ids , which I would like to keep in date order, whilst easily discerning which row corresponds to which customer.
I have just got used to using the filter facility to separate desired rows. and may just use that to add colour to like rows.
Thanks again for your help

Neil
open office 4.1 on windows 10, 64 bit
neiloneil
 
Posts: 8
Joined: Fri Apr 27, 2018 1:27 pm

Re: How should I apply conditional formatting to achieve thi

Postby keme » Tue Oct 02, 2018 8:23 pm

There are several approaches to this kind of formatting.
  • Each entity (customer) has its own unique color. For this to work, you must create one style for each customer. (Three with Villeroy's approach, but with a bit of careful design work it is possible to defeat the number format part of the style, so you can use the same style for all columns)
  • Change color whenever there is a row with a different customer from previous row. With this, you only need two styles to alternate.
  • Have a selection field to pick one customer to highlight. For this you only need to define one style.
Do you need to have a distinct color for each customer, clear separation of rows for each customer, or just a way to quickly highlight all entries for any chosen customer?
User avatar
keme
Volunteer
 
Posts: 3265
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How should I apply conditional formatting to achieve thi

Postby neiloneil » Tue Oct 02, 2018 11:58 pm

just a way to quickly highlight all entries for any chosen customer would be fine.
open office 4.1 on windows 10, 64 bit
neiloneil
 
Posts: 8
Joined: Fri Apr 27, 2018 1:27 pm

Re: How should I apply conditional formatting to achieve thi

Postby Lupp » Wed Oct 03, 2018 2:35 am

neiloneil wrote:just a way to quickly highlight all entries for any chosen customer would be fine.

If the "chosen customer" is the one whose key is placed in cell C2, this is exactly what my demo does.
If you want to choose otherwise you need to dedicate a specific cell where you enter the key. The condition of the CF needs a replacement of the $C$2 with the absolute address of the "cell for choice" then.

(Please try to be precise about any relevant detail of your question. We should minimize guessing.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How should I apply conditional formatting to achieve thi

Postby Zizi64 » Wed Oct 03, 2018 7:10 am

just a way to quickly highlight all entries for any chosen customer would be fine.

An another approach:
The usage of the Filter feature is not appropriate for you?
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.3.2; 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: 8460
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How should I apply conditional formatting to achieve thi

Postby neiloneil » Wed Oct 03, 2018 9:36 am

yes it will be adequate. Later I will research it some more maybe. It's not that I need it, I like to learn better ways to do things.
open office 4.1 on windows 10, 64 bit
neiloneil
 
Posts: 8
Joined: Fri Apr 27, 2018 1:27 pm


Return to Calc

Who is online

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