Data Validation Hide Previously Used Items in Dropdown

I would like to take my son's little league baseball team roster and create a drop down list that shrinks as it is used. I will try to explain more clearly below and have attached a mock file that would help best.

I have 12 players with uniform numbers 1-12. I have named the players names "PLAYERS" and used a VLOOKUP to access their uniform numbers in the defined group called "UNIFORMS". For each inning, I would like to make sure I use each field postion/player once and only once so as the position/player is used up it doesn't show up in the validation drop down list again (I also defined the positions as "POSITIONS"). This prevents me from placing two different players in the same position within the same inning and creating confusion at game time. Column D is an example of a good setup with all positions/players used once and Column E is an error because of multiple posistions. Note: There are supposed to be (2) SIT positions as this age group has 10 field players and 12 players per team which means two players will need to sit each inning.

G'day

Just what's based on your title thread try this

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

I will be back later after work if more help is needed.

Cheers

RC

Exactly what in the tutorial posted by ratcat is unclear?

I tried to apply the priciples but kept getting errors mostly about loop conditions. I just need a little help to get going. Something to let me know what the format should look like and I can take it from there.

Also, is it possible to hide information based off previously used items in both the column and row information? For example, each player can only play one position per inning, which is the original request of this thread. But, each player can only play the same position twice per game. So as you can see, if a player is in the "P" position in the 1st and 2nd innings, he can't play their again the rest of the game. So I was wondering if there was a way to accomplish both conditions at the same time.

Jim

Have a look at the attached.

Basically I did as per the Contextures tutorial.

You need to repeat the process for each inning.. with 6 independent sets of formulas and named ranges.

For the SITs, I would suggest you add 2 more items to list in Column A, called something like SIT1 and SIT2 and expand all the formulas and named ranges and data validations to include these 2 items.

For now ignore all the formulas in columns J onward....

In the sheet I attached, go to D3 and pick a position from the dropdown list.

Now go to D4 and click the dropdown arrow.. do you see the position that you selected in D3 appear in the list? You shouldn't.

The formulas in J onward are all used to determine this condensed list setting.

You can hide those columns if you wish, but don't change them, unless you are adding to what you have in Column A.

The data Validation list you had called "Positions" is no longer valid for columns D to I.

My extra suggestion for the 2 SIT positions is to go to column A in the Batting order sheet and add SIT1 and SIT2 to cells A15 and A16, then you would adjust all the formulas in Columns J to U to accomodate the new range and you would need to adjust the named range formulas for each of the named ranges: Inning1 to Inning6... then you would add data validation to rows 15 and 16 in columns D to I by copying formats from row 14...

Try this:

Change formula in J3 to: =IF(COUNTIF(D$3:D$14,POSITIONS)>=1,"",ROW()-2) and copy it down to J14, then copy them across to column O.

Change formula in P3 to: =IF(ROW(A3)-ROW(A$3)+1>COUNT(J$3:J$14),"", INDEX(POSITIONS,SMALL(J$3:J$14,1+ROW(A3)-ROW(A$3))))

and copy down to P14 and across to column U.

In Sheet2, change SIT in C11 and C12 to SIT1 and SIT2, respectively.

Now test the data validations... is that what you want?

Hopefully, I got my act together this time.

Change formula in J3 to:

=IF(COUNTIF(D$3:D$14,Sheet2!$C1)>=1,"",ROW()-2) and copy down and across to column O

Formula in P3 should remain as:

=IF(ROW(A3)-ROW(A$3)+1>COUNT(J$3:J$14),"", INDEX(POSITIONS,SMALL(J$3:J$14,1+ROW(A3)-ROW(A$3))))

copied down and across.

Remember to change the SITE cells in the other sheet.

In V3:

Please Login or Register  to view this content.

copied down

In W3:

Please Login or Register  to view this content.

Then create a new named range: PlayerList with formula:

Please Login or Register  to view this content.

Then change data validation in B3:B14 to =PlayerList

G'day All,

Without reading through all the thread I look a NBVC attachment and seems to be a couple of typos

Here's a working example


Edit: About NBVC attachment Just worked it out its amazing by just missing a $ sign can screw things up

Try this at J3 =IF(COUNTIF(D$3:D$14,$A3)>=1,"",ROW()) and copy down and across to Col O and the sheet will start to work

You are welcome.

Ratcat, I know the last attachment had some issues... but I think we cleared them up after post 12 above.

Okay ! that's great news NBVC and for Jim who seems to grasps the workings of the formulas to achieve the result he's looking for.

Btw it took me about half an hour when I first learn it to get it working lol

Cheers

RC

It seems you made the rows absolute instead of the column in formulas starting in column U

So U3 should be:

=IF(COUNTIF(D$3:D$14,$AH3)>=1,"",ROW()-2)

copied down and across to Z14

You are welcome,

Please note that if you are going to do some separate tables below with then the formulas in say U18, would need a bit of adjustment.. i.e. =IF(COUNTIF(D$19:D$30,$AH19)>=1,"",ROW()-18)

and in AA18:

=IF(ROW(A19)-ROW(A$19)+1>COUNT($U$19:$U$30),"", INDEX($AH$19:$AH$30,SMALL($U$19:$U$30,1+ROW(A19)-ROW($A$19))))

assuming you have separate Player lists, etc in same relative positions for that column

Originally Posted by Sridhar Kollepara

Hi All,
I have similar need to hide used items from drop drown list like selecting the base ball team for multiple innings.
The concern is in my application i have unlimited innings i need to choose the teams for. Is there any VBA codes to achieve this.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread