Hide Used Items in Drop Down List

Hi all,
I have made a list of 29 players for my Fantasy League and there are six lists (one for each position) and when the user picks a position, only certain players will appear in the list. However, when a position is used more than once, I want the player to be available only once, so he cannot be repeated in the team. For example, in midfield, I have 5 players (player A to player E) and there are two midfield positions. When I select the first one, the list appears with players A-E and I select player B. Then, for the second one, the list appears with players A,C,D,E. Is this possible to happen? I have attached the document below. I have tried using: http://www.contextures.com/xlDataVal03.html but it is making no sense to me. I would be very grateful if someone could tell me what to do or complete a sample.

Thank you!

A League of their Own (1).xlsx

Have a look here http://www.contextures.com/xlDataVal03.html

Windy

Originally Posted by ChemistB

It is but then you are going to have to do something like suggested on the link that confused you.

Sure, Are the substitute and reserves picked from the entire squad or will there be one from this group, two from that group kind of thing? Sorry, I didn't scroll down and see those!

Hi mate, it doesn't matter about the white blank space! I'll just leave that, it looks fine how it is! The substitutes and reserves can be picked from any of the remaining players so it isn't determined by position except the first substitute which is a goalkeeper!

How about this...

Have created named ranges for each group plus one covering all remaining players.

Have added message to data validation which appears when a cell is selected.

Windy

Originally Posted by windy58

How about this...

Have created named ranges for each group plus one covering all remaining players.

Have added message to data validation which appears when a cell is selected.

Windy

Hi mate I really appreciate it but it gets confusing for me looking at that. I would rather use ChemistB's technique but if I can't then I will use yours mate. Thank you.

Originally Posted by ChemistB

Okay, since the reserve lineup would not have looked good with all those blank spaces, I decided to clean it all up.
Here's what the new formulas do and then I'll give you the formulas. We'll look at goalkeepsing in a 3-4-3 formation (Column E of Squad)
It compares each goalkeeper in A3:A5 to see if it's already been used, if not it assigns a number from 1 to 3. If it's been used, you get an error (which Excel later ignores). So if John Ruddy was used, it comes up with 1, Error, 3. It commits these to member (as an array). Then the formula says pull the smallest value and put it in E3, second smallest in E4 and so on.

The ARRAY formula in E3 copied down to E5 is

=IFERROR(INDEX($A$3:$A$5, SMALL(IF(ISERROR(MATCH($A$3:$A$5, '3-4-3'!$C$10:$C$20,0)), ROW($A$3:$A$5)-2), ROWS($A$1:$A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I used this same technique to make a seamless list of reserves starting in E33 to E5
See attachment. Is this good for you?

Hi mate! Thank you, it looks great but I am not sure whether to keep the top bit of the "Squad" page or do I just need the bottom bit? Also, do you think it would be best if I just duplicated the document to create a new team rather than sit through the formulas?? I really appreciate it

Originally Posted by ChemistB

You would need to keep both portions since the top bit is a series of smaller lists that shift as you use up the members within. I would advise you to use this as a template and just create new teams. Ranges might need to be shifted if they have a lot more players.

Hi mate. No, everyone is just going to have the same amount of players, so should I just keep everything as it is and change the players only? Thanks a lot!