Can you have a google sheets dropdown list that is filtered by prior selections

Question

I am trying to do some team management for my sons sports community. The challenge is we have a list of 60 players, and need to split them into 4 teams. What I would like to do is have 1 column for each team, and in the players area have a dropdown list of the available players. The catch is I would like the player removed from the dropdown list once they have been assigned to a team. I have succesfully made the dropdown list for each team, but can't seem to make the list change as players are added. Can anyone help?

Answer 1

Here is a solution https://docs.google.com/spreadsheets/d/1-MWiKINX_mSJXRenmscofYRG5gJAWuyXfIRIYT5BGdc/edit?usp=sharing

Make the matrix with 4 columns (4 teams) Make the list of attendees Check if the attendee is already assigned by (cell B1 of attendees)

={"Status";arrayformula(IF(COUNTIF(teams,A2:A)>0,"nok","ok"))}

Then filtered the list as following (cell C2)

=filter(A2:A,B2:B="ok")

This will done your validation list. By choosing each participant, the list will reduce and you will see a top-right red corner, that means that this participant is well now out of the list of validation.