I am working on a sheet for a game I play and having some trouble with cell coloring.
I have one sheet, named "PlayerList" which maintains a list of player names, class and role. Players are frequently added/removed from this list. This sheet has the format of:
Column A Column B Column C Name Class Role Player1 Mage Damage Player2 Paladin Heal Player3 Warrior Tank
Classes have specific colors associated with them (e.g. Mage = blue, Paladin = pink, Warrior = brown, etc)
I have a second sheet which references the PlayerList sheet, and creates a full list of names with QUERY via the formula:
=QUERY(PlayerList!A3:C5,"SELECT A WHERE C = 'Tank' OR C = 'Heal' OR C = 'Damage'") generates: Player1 Player2 Player3
I would like to have each player's cell automatically colored depending on their class in PlayerList (e.g. if Player3 is a Warrior, then his name cell is brown). My initial thought was to use multiple Conditional Formatting rules with custom formulas (one for each class color), however, I am not really sure what the formula should be. Is it possible to implement colors when using IF() in a custom formula rule? Or is there a better way to go about this?
The best I could come up with was to make multiple Conditional Formatting rules that check individual player names, however, this is not ideal as I then have to manually adjust the formatting rules every time a player is added/removed from PlayerList.
Here is an example sheet if it helps:
I have manually colored the names on the second sheet, but I would like them to be automated.