Update Validation List

[Guest]

I am trying to create a spreadsheet for a fantasy fotball draft. I have a
validation list with all of the available players.

How do I remove a player's name from the list once they are drafted (picked
from the list)

 

[Bob Phillips]

You need another list of unpicked players, using a formula like this

select cells G1:G10 say and in the formula bar add

=IF(ISERROR(SMALL(IF(B1:B10="x","",ROW($A1:$A10)),ROW($A1:$A10))),"",
INDEX($A$1:$A$10,SMALL(IF(B1:B10<>"x",ROW($A1:$A10),""),ROW($A1:$A10))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

where x denotes drafted

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Debra Dalgleish]

There are instructions here for hiding used items in a data validation list:

http://www.contextures.com/xlDataVal03.html