Automatic cell coloring via conditional formatting

Question

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:

https://docs.google.com/spreadsheets/d/1XpKaY6Px_uN8EjkuY58ejBshZ8EcEDhnRgEWbnAZnMI/edit#gid=0

I have manually colored the names on the second sheet, but I would like them to be automated.

Answer 1

• blue:

  =IF(VLOOKUP(E2, INDIRECT("PlayerList!A3:B"), 2, 0)="Mage", 1)

• pink:

  =IF(VLOOKUP(E2, INDIRECT("PlayerList!A3:B"), 2, 0)="Paladin", 1)

• orange:

  =IF(VLOOKUP(E2, INDIRECT("PlayerList!A3:B"), 2, 0)="Warrior", 1)

  ---