Defining color from a list

[bakroi]

Hi,

I don’t know if I’m going to express myself well…

I have a list of “words” on one page (acually its names). And on another page I have cells that I want to get a color corresponding to that name on the first page.

My question, is it possible to for exemple define 40 color settings, and link them to these names. So when I type on the second page somewhere in that cell one of the names, defined on the first page, that cell takes the color defined…?
Is it clear?

I have an example attached… maybe that makes it a little clearer…

And if possible with no such formula as: if cell contains “color 1” than color red, if not, if cell contains “color 2” than color yellow, if not, if …

It’s a long list

thanx

and sorry for the poor english!

greetz

color formula.ods

[Lupp]

(No offense intended! The example you give does not look as if you know the related basics.)

Do you know that there are 16777216 colors technically distinguishable? Most (next to all) of them you cannot identify by a name. Even the 167 names used for the standard palette are not specified in a mandatory way. You may change their meaning by editing the palette. You can define a huge number of groups of colors not distinguishable for a human eye - or for a specific screen. Even some pairs of colors given a different name in the palette are not distinguishable on my screen for my eye.

Thus: To define a color to be identified in an unmistakable way for your sheet you have to define a named cell style. This done for the colors you actually want to use you have two ways (in principle) to achieve something like you described:

1. Use conditional formatting.
2. Use the STYLE function.
3. There is a trick based on undocumented behaviour to combine both these ways I would not recommend.

To describe the proceedings to someone not yet familiar with some basics would require to write a tutorial.
You may look into the attached example and analyse its working, however.

Abandon the idea to implement your kind of coloring for many colors. There are too many disadvantages, and it would (imo) not work without a certain amount of coding macros.

(Also abandon merging of cells wherever possible. Mostly it is possible.)

[bakroi]

Hello,

First of all, thank you for the answer.
I think I wasn’t clear enough. But the colors dont really matter wich one it is. I think I can find 140 colors relatively distinguisable.
I allready found the way you showed me. But it’s to much work to do it that way. I was wondering if there was an alternative way.

I made a simple exemple to show the problem. But it comes from a more complex sheet.
If you really know someting about the color make-up, I can send you the sheet and you could look at it.

If the way to do it is to complicated, I’ll do it by hand. (and it looks like it’s complicated)

pm me if you would like to see the original file.

greetz and thank you!