0

I'm coaching a soccer team with 36 players and have a sheet in which I have all the players names in column A. Next to their names (in column B) I've set a ranking value from 1-5 where the best players have a 5.

On a weekend we may have 3 games and I pick 3 teams of 10 players from the 36 players and list the players of the 3 different teams in column D, E and F.

I'd like to format the backgrounds of the cells in columns D, E, and F with 5 different colors depending on the ranking of the player. For example if a player named John has a 5 in rating the background color should be green, if ranking is 4 then yellow and so on.

Also if possible, I'd like to sum up the total ranking points of a team in the cell after the last team member, (we're always 10 in each team). All this in order to easily see if the teams are fairly equal.

Would be really happy for assistance :)

here is a link to the sheet https://docs.google.com/spreadsheets/d/1cm-i1OIVoxE7eQdKC3o8RtOzcxoJoLaw1esXL2yMBGA/edit?usp=sharing

user1425385
  • 127
  • 2
  • 10
  • You can do it with conditional formatting. – ziganotschka Apr 07 '20 at 15:35
  • share a copy of your sheet – player0 Apr 07 '20 at 15:44
  • If you can't share a copy of the sheet you will need to provide a sample sheet with sample input and a sample output, basically before and after of what you want it to look like. – CodeCamper Apr 07 '20 at 16:21
  • @ziganotschka yes, but how can I do it in a smart way so that I dont have to to "If text is exactly" on each and every player name? I'd like it to be smart enough to recognise if I add a new player to the squad (name and ranking in column A & B) and give him a ranking of 4. Then whichever cell I enter his name in will get the right background color and value assigned to it. Basically I want to assign a value to a text string and apply corresponding bg-color and and sum up the values in the cell below the last name in the column. – user1425385 Apr 08 '20 at 12:33
  • Then you need to use a script, for which you need Apps Script knowledge. Here is a [sample](https://stackoverflow.com/a/61009931/11599789). – ziganotschka Apr 08 '20 at 12:37
  • @ziganotschka unfortunately I've no experience of Apps Script so I'm still stuck – user1425385 Apr 08 '20 at 21:42

0 Answers0