[Solved] Set cell colors based on dropdown selection

[Noobian]

First of all.... hi to all of you!

Im messing around with LibreOffice and I got into a deadend. My problem is, that i want to make a charactersheet for an rpg. Cyberpunk2020 if you want to know

So i made a dropdown menu, where the player can choose his role. Solo, Cop, Fixer, Hacker etc... and what I want to achieve is that when the player choose Solo, some of the skills get colored, if he chooses Fixer, a different set of cells get colored. I don't know if its even possible, but some of you LibreOffice masters will have an answer for me

Thank you in advance!

Edit: Changed subject, was Hi all! I need some help  Make your post understandable by others  -- MrProgrammer, forum moderator

[Zizi64]

Please upload your ODF type sample file here.

Please study the "Conditional formatting" feature and the "Cell Styles".

[Noobian]

how do i upload the file? sorry im new around here

[Zizi64]

Use the Post reply (not the Quick reply). You can fint the Attachment TAb under the message edit box. The file size limit is 128 KiB in this Forum. You can share the bigger files on a free file sharing service like the Google drive. Just put the link into the message.

[Noobian]

Cyberpunk karakterlap.ods

(42.11 KiB) Downloaded 140 times

so this is what im working on. I was checking out the conditional formating and cell style as you advised, but I didn't get it, how to use it. As far as I understood i can use max 3 condition.

So on my sheet you see "szerep" which is a dropdown menu, from there you can select various options and I would like for various "képzetség" to get color depending on what "szerep" did you choose. Hope this makes sense

[Zizi64]

As far as I understood i can use max 3 condition.

It is true in the Apache OpenOffice. But you can use more than three conditions in the LibreOffice.

[Noobian]

ooooh i see. I have LibreOffice installed but honestly, i don't know which option to choose to set the condition depending on the selection of the dropdown menu.

[Zizi64]

when the player choose Solo, some of the skills get colored, if he chooses Fixer, a different set of cells get colored.

Can you descript which cells should be colored when you choose Fixer?
And which cells should be colored when you choose Solo?
Etc...

Can you give us a clear rule? And where will be stored these informations?

[Zizi64]

LibreOffice, unspecified version

The version of the LO is important in this case. The Conditional Format feature was changed after the LO 4.x.x version. The CF consecutive cells/ranges will be grouped (concatenated) automatically in the newer versions.

[Noobian]

I don't know where I have to store this. I just wanted to highlight the so called career skilss for the player. If you or someone can show me how to do it on one role. I can make it for the others.

Szóló has:
Harcérzék
Éberség/Észlelés
Pisztolyok
Verekedés or Harcművészet
Közelharc
Fegyverismeret
Puskák
Atlétika
Géppisztolyok
Lopakodás

These skills should be highlighted when its selected from the dropdown menu

[Zizi64]

The CF can work based on a clear "database" only (it is not a real database really). It is not a good idea to store these informations in the conditions of the CF feature. (because it means many-many different condition groups.) You can use some helper cell ranges on some helper sheet/s/ for this task.

[Zizi64]

The Hungarian Forum:

https://forum.openoffice.org/hu/forum/

(But there are more chance for a real solution in this (English) Forum)

[Zizi64]

Is it same problem?

https://forum.openoffice.org/en/forum/v ... p?t=107784

[Noobian]

well it seemsw that other forum post is about the same problem i have. But i don't see a solution for it. I guess i have to practice this conditional formatting and styling, cause i never used it.

És köszönöm a segítségedet

[eeigor]

It is not clear. Translate into English and explain what needs to be done.

[Zizi64]

It is not clear. Translate into English...

És köszönöm a segítségedet (HU) = And thank you for your help (EN)

[Noobian]

It is not clear. Translate into English and explain what needs to be done.

So what i would like to make is that some cells from the "Képzettség" gets highlighted when a role is selected from the "Szerep" dropdown menu.

[Zizi64]

Here is the sample file modified by me. Please fill in the cells on the Helper Sheet:

Code: Select all

Harcérzék:Szóló,

List all of character names at the "Harcérzék" who has such skill:

Code: Select all

Harcérzék:Szóló,Rocker,

...or what you want...

Do it for all of the skills on the Helper sheet.

See the Conditional format conditions on the Sheet "Játékos karakterlap". (Created in LibreOffice 6.1.6!!!!!)

Choose an another character. The CF works (I hope).

Cyberpunk karakterlap_Zizi64.ods

(27.37 KiB) Downloaded 139 times

In my opinion these "colorized partial cellranges" is not the best data structure for a pseudo-database.

[Noobian]

maaaaan you made it, i See

so here are all the skill sets for various roles:

Szóló:
Harcérzék
Éberség/Észlelés
Pisztolyok
Verekedés
Harcművészet
Közelharc
Fegyverismeret
Puskák
Atlétika
Géppisztolyok
Lopakodás

Céges:
Befolyás
Éberség/Észlelés
Emberismeret
Műveltség
Adatbáziskezelés
Viselkedés
Meggyőzés/Rábeszélés
Tőzsdézés
Öltözködés és Divat
Testápolás

Média:
Szavahihetőség
Éberség/Észlelés
Kompozíciókészség
Műveltség
Meggyőzés/Rábeszélés
Emberismeret
Viselkedés
Alvilág ismerete
Filmezés és fényképezés
Interjú

Nomád:
Család
Éberség/Észlelés
Állóképesség
Közelharc
Puskák
Vezetés
Barkácsolás
Túlélés
Verekedés
Atlétika

Technikus:
Berhelés
Éberség/Észlelés
Barkácsolás
Cyber Tech
Oktatás
Műveltség
Elektronika
Gyro
Aero
Fegyverismeret
Biztonságtechnika

Zsaru
Tekintély
Éberség/Észlelés
Pisztolyok
Emberismeret
Atlétika
Műveltség
Verekedés
Közelharc
Vallatás
Alvilág ismerete

Rocker:
Karizmatikus Vezetőkészség
Éberség/Észlelés
Előadókészség
Hangszer
Öltözködés és Divat
Kompozicókészség
Csábítás
Meggyőzés/Rábeszélés
Alvilág ismerete
Verekedés

Orvtechnikus
Orvoslás
Éberség/Észlelés
Barkácsolás
Diagnózis
Műveltség
Kriog.tartály müködtetése
Adatbáziskezelés
Gyógyszerészet
Zoológia
Emberismeret

Fixer:
Seftelés
Éberség/Észlelés
Hamisítás
Pisztolyok
Verekedés
Közelharc
Zárnyitás
Zsebmetszés
Megfélelmlítés
Meggyőzés/Rábeszélés

So these are all the roles that are available in the game. I hope its not a problem i postit here.

[eeigor]

Something is not in the Szerep drop-down list:

Rocker
Szóló
Hacker
Technikus
OrvTechnikus
Média
Zsaru
Céges
Fixer
Nomád

What are the characteristics of Hacker?

So what i would like to make is that some cells from the "Képzettség" gets highlighted when a role is selected from the "Szerep" dropdown menu.

Done. Try it.
Made purely mechanically. But I have not experienced any difficulties. Except only in the selection of target ranges (C6:C15;C17:C21;C23:C29;E6:E28;G6:G25;G27:G28;I6:I8;I10:I29), which are interrupted because they contain some headers (Is this true?) with the merging of two row cells.
Formula is

ISNUMBER(MATCH(C6;INDIRECT($B$2);0))

where $B$2 refers to named range (role).

[Noobian]

wow! thanks! I was trying to first make it like this, but I never thought I have to make another page. What do you guys say, what is the best way to do this?

[Noobian]

Also yes, I merged some cells, to fit better. So probably thats what is causing the problem

[eeigor]

Named ranges can also be created directly in the formula expression, but then it is inconvenient to view and edit it. However, this is possible if the skill set is fixed (complete list).

[Noobian]

it is set for the base skills. those are the ones i would like to highlight. You prefer conditional formatting, or how you did it?

[eeigor]

Yes, it’s conditional formatting.

[Noobian]

oh I see... Eversince it was first advised to me to use that, i try to make it. But no success yet. I make the cell style, I select all the cells i want to have that style, but then i got lost how to apply the condition.

[eeigor]

But now you have a working example to investigate on your own.
ISNUMBER(MATCH(C6;INDIRECT($B$2);0))
C6 is RC in Excel R1C1 formula syntax (relative notation), which means: in the current Row of the current Column. The difficulty of understanding is exactly that.
But $C$6 is really cell C6. But this is not obvious.

[Villeroy]

Conditional formatting is a highly advanced feature. It presumes that you are familiar with all the fundamental aspects of your spreadsheet application. Text vs. number, value vs. formula, values vs. formatting, absolute vs. relative referencing, positional function arguments, boolean logic (what is "true"?, order of conditions). What makes it even more difficult to use is that you can't see why it fails until you test your conditions thoroughly in sheet cells.

[eeigor]

Named ranges can also be created directly in the formula expression, but then it is inconvenient to view and edit it.

@Villeroy, as I said above, assuming this was possible, I converted the named range to a named formula expression instead of referring to a range of cells, but this doesn't work in Calc, and should work as in Excel. Let me explain the point of the conversions...

Create in 'Manage Names' dialog:
For example
ByColums:
Szóló: {"Harcérzék";"Éberség/Észlelés";"Pisztolyok";"Verekedés";"Harcművészet";"Közelharc";"Fegyverismeret";"Puskák";"Atlétika";"Géppisztolyok";"Lopakodás"}

Or ByRows (never mind):
Céges: {"Befolyás"|"Éberség/Észlelés"|"Emberismeret"|"Műveltség"|"Adatbáziskezelés"|"Viselkedés"|"Meggyőzés/Rábeszélés"|"Tőzsdézés"|"Öltözködés és Divat"|"Testápolás"}

Works (returnes 10):
=ROWS({"Befolyás"|"Éberség/Észlelés"|"Emberismeret"|"Műveltség"|"Adatbáziskezelés"|"Viselkedés"|"Meggyőzés/Rábeszélés"|"Tőzsdézés"|"Öltözködés és Divat"|"Testápolás"})
Or:
=ROWS(Céges)

Don't work (display #REF!):
=ROWS(INDIRECT("Céges"))

Why?!

So my example without an auxiliary sheet refuses to work, because INDIRECT() does not want to recognize a reference not to a named range, but to a named formula expression.
Is it a bug?

Edit:
Perhaps this is a question for the developer @erAck. And it's not really a bug. Then the creation of an extra sheet is not only reasonable for the convenience of its maintenance, but also for the technical features of Calc.

[Villeroy]

I just tried to point out that someone who calls himself "Noobian" may not be able to transfer a given solution.