Conditional formatting for many dropdowns, each with many values

[Jordan]

I have a cell range of dropdown options (10 options), and each option corresponds to a style that I’ve created.

I have many dropdowns (20+) with these options. When a value is selected in that dropdown, I’d like the dropdown cell’s background colour to change to the style that corresponds to that dropdown value.

In the attached Calc demo file, you can see that I’ve been able to do this for the cell next to the dropdown, but I cannot seem to apply it to the dropdown cell itself.

I know I could accomplish this with conditional formatting, but I’d need a conditional rule for each dropdown value for each dropdown, so in the case of this simple demo file I’ve attached, that would be 200 rules, which is simply unmanageable.

Is there any other way to accomplish this without using macros?
Dropdown Colours.ods (20.5 KB)

[F3KTotal]

Hello,
why don’t you customize your toolbars or contexmenue? You can add your styles there. If you change the icon of a toolbar entry, it appers also in the contextmenue.

Dropdown Colours.ods (17.0 KB)

[JohnSUN]

That’s right, you’ve already done three-quarters of the work - created styles with the right names, created a list with a list of these names, and know the formula for extracting the desired style name from the list. It remains to apply conditional formatting to cells:

And yes, WELCOME @Jordan !

[mariosv]

If I remember fine, some comments of developers, the use of STYLE function inside CF it’s not a good practice. (95233 – UI: Use of STYLE() in conditional formatting results in continuous CPU usage)

[erAck]

That comment is about an even weirder use of STYLE() in CF yielding a racing condition between applying styles from STYLE() and the CF.

JohnSUN’s approach suppresses the result with T(STYLE()) to an empty string and thus the CF formula result is never TRUE so the CF style is not applied, so this may work ok in this case…

However, the use of STYLE() itself without compelling reason is not good practice, even less in conditional formatting, if one does not know exactly what s/he’s doing. As a reminder and to internalize let’s paste the note from the latest Spreadsheet Functions help on this:

The STYLE function should not be used without compelling reason, its purpose is the use with asynchronous Add-In functions to visually notify about the availability of a result. In almost all other cases using conditional formatting instead is a better choice.

[JohnSUN]

@mariosv

the use of STYLE function inside CF it’s not a good practice

If I remember well, this trick was posted in 2009 (maybe even earlier). The STYLE() function, which is not found in other office suites, does a lot. Yes, at one time the combination of this function and conditional formatting led to problems. But before posting my answer, I checked if the solution would work today. Neither for 20 cells from the test table, nor for 100 cells, nor for 1000, nor for five sheets with 1000 cells on each, I did not see the problem described in that discussion. I do not know at what point and by whose efforts this problem was solved, but I am sincerely grateful to him for this. Perhaps I was just lucky and it is on my computer and with my configuration that the error does not appear, I don’t know

By the way, I do not see another way to get around this limitation:

@Jordan

without using macros

[mariosv]

The comment was only thinking of someone that seeing the solution, not using it in an intensive way, with maybe some slowness, and asking themselves from where it comes.
I have used it sometimes.

[karolus]

Hallo
the same Approach as @JohnSUN
Dropdown Colours_CF.ods (17.5 KB)