How should I apply conditional formatting to achieve this?

[neiloneil]

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?

[Zizi64]

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.

[RoryOF]

There is an extension to OO that permits more conditional formatting
Extend your conditional formatting

[Villeroy]

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

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.

[Lupp]

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:

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.

[Villeroy]

I was assuming that the real data have more than 2 different customers in column C.

[Lupp]

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.

[neiloneil]

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

[keme]

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?

[neiloneil]

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

[Lupp]

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.)

[Zizi64]

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?

[neiloneil]

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.