Can only select a drop down option once

I'm trying to make a last man standing football excel sheet.

This is for UK football and the idea is each person selects a football team each week, If there team wins they move onto the second week, if they lose they are out.

When you select a team for 1 week you are not allowed select that team again until the whole thing is over.

Is there a way to code into excel so when someone selects a team form the drop down menu that they can not select that team again until all data is removed ( reset )

the way i've the sheet setup is i've all the players playing the game in column A from A1 - A12
I've the word Week 1 - Week 2 etc in Row's B1 - M1 ( 12 weeks )

I've listed all 20 Up premieleage teams in column X just to have them on the same sheet so i could make the drop down menu,
I copied the dropdown menu into B2/B3/B4 for testing, But I can't really do much more without having the above setup.

So can anyone help me ?

thanks.

Debra Dalgleish shows how you can hide previously-used data-validation items here:

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

Perhaps you can adapt this to your situation.

Hope this helps.

Pete

I had a look at that and it wont work as the players below are allowed select the same team as any other player. They just can not select the same team the next week.

I've attached my spread sheet. I've just added 3 players and I added all the teams. I've listed all the teams in column Z this time ( no reason )

would the code have to 1st check the players selection.... then check the list in col Z then figure out what is left for the player to select ?

Hi thanks for your help.

But it's not working.

If player "Dave" Selected "Arsenal" then the other players can not select it. The other player should be allowed select it. Any player can select any team once they have not selected it before. It doesn't matter if someone else picks it in the same week.

Also If Dave selects Arenal in week one - it doesn't let him select it in week 2. This is correct. But in week 3 he's able to select it again. This should not be there as once he selects a team he's blocked from selecting that team again.

Also i tried to paste what you said into the area's you said but i got errors pop up.

Hi,

No - When i select weeks 2 / 3 / 4 etc all i see is people's names and letters. This needs to be the teams.

The idea of last man standing is to pick one football team each week. If your teams wins then you go on to week 2 and select a new team ( can't be the team you picked last week ) If you lose then you are out.

So Week 1 should have all the teams
week 2 should have all the teams apart from the team selected in Week 1. ( each player could have selected a different team )
Week 3 will have all the teams apart from the team selected in week 1 and week 2 and so on .

This will be different for each player as they can pick any team each week ( out of what is left)

I tried the attachment, thats the only way I was able to test it.

I wouldn't following the copy and paste as it was kinda asking me to select areas ,, I think.. but i didn't know where to select.

I'll try replacing them as you said.

Originally Posted by Fotis1991

No... I didn't do anything for the others weeks....

My last example is to see if columns A & B is OK, and then to see the others.

So A & B Columns are OK??

A will have all my friends names in them

B Will have the football teams. C / D / E /F etc will also have football teams in them but will not have any teams already selected from each person,

There will be about 15 people playing and there are 20 teams to pick from.

Yes this is working

Each week I can not select a team that I've already selected this is great

Anyone can select any team they like, We are working of the English Premier League Football fixtures - There will be 10 football matches each week. We ( me and my friends ) are trying to pick teams we think will win.. It's not to hard at the start because you know who the good teams are.. But when you select the good teams you can't select them again so you have to pick wisely.

How to I add more players to this ? I tried to highlight it all and drag down.. but that doesn't work.

I can't see any of your code so i've no idea what you did to copy it.

Also i dont know what helper columns mean.

Something similar but slightly different. Each player needs a list with the clubnames that remain available to him. These lists can be found in columns AA, AB, etc. Such columns can be called helper columns. The list for Dave is a named range "Dave", etc.., press ctrl+F3 to see a list of these named ranges.

thanks guys.

@WHER - I will have more then 3 players when I'm starting this.

How do i copy it so i can add more people and not mess up the coding.

I tried to do it but it didnt work.

In short, to add a player:
Enter his name in A5
Select AC2, copy/paste formula in AD2, change one part of formula (in AD2): $B$4:$M$4 >> $B$5:$M$5, confirm formula with ctrl+shift+enter instead of simply enter.
Drag formula in AD2 down to AD21
For (simplest) datavalidation in B5:M5: select B5:M5 >> add datavalidation >>list >>Refers To:select AD2:AD21
The datavalidation in my example involves dynamic named ranges, which is more complicated but not essential.

Yes i think this is working

Now that this is figured out I need more things added. Going to start a new thread.

Small problem with this..

I can't reset it.. Once i select a team and it goes on a few weeks I can't reset the thing to all blanks again.. Is there a way to add a Blank field ?

I tried to do it myself but i messed up the code and it stopped working again.

I figured it out

thanks.