How do I build a Soccer position/lineup template?

[Guest]

I am attempting to build a soccer position/lineup worksheet that will allow
me to not have any DUPLICATE players on the field at a given time.

How do I best accomplish this? Is it possible within Excel to do this?

 

[Bernie Deitrick]

D,

Let's say that you enter your players' names for each game's lineup into cells E2:E12, and you have
a list of all the players' names on the same sheet in cells B3:B25 (You've got a BIG team).

Select cells E2:E12, use Data / Validation.... Select Custom under "Allow", and enter the formula

=AND(NOT(ISERROR(MATCH(E2,$B$3:$B$25,FALSE))),COUNTIF($E$2:$E$12,E2)=1)

You could also apply Conditional Formatting to your list of players in B3:B25 to shade them after
their name has been entered into E2:E12 (to better show who is still available). Select B3:B25, then
use Format / Conditional Formatting... Formula is.....

=NOT(ISERROR(MATCH(B3,$E$2:$E$12,FALSE)))

and then set your shading.

HTH,
Bernie
MS Excel MVP