A Drop Down List That Reduces After Every Selection

**red61** · 06-22-2013, 06:13 AM

Hi,

I am having a problem figuring out how to create a drop down list that reduces after every selection. I am trying to create a spreadsheet that records individual players selections of team names. The team names cannot be selected again until all the team names have been used.

I have created the drop down list using Data > Validation > List > the list itself, but I can't figure out how to make the next selection reduce.

I have browsed the forum site and found that I can use the OFFSET function with a nested COUNTA function, but all I get in the output is ####.

Do I have to use the formula in the Data > Validation > List dialogue box and use a name for the team names?

I am using Excel 2003.

Thanks for any help I can get.

**benishiryo** · 06-22-2013, 07:10 AM

hi red61, welcome to the forum. i have attached the worksheet for you. first, do up your list for the drop down. mine is in A1:A6. then i did an array formula in B1:B6.
=INDEX($A$1:$A$6,SMALL(IF(ISNA(MATCH($A$1:$A$6,$E$1:$E$6,0)),ROW($A$1:$A$6)-ROW($A$1)+1),ROWS(B$1:B1)))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.

this list will shorten when the user selects 1 of them for the dropdown. but my drop down needs to reduce whenever a selection is made. so i did a dynamic range using INDEX. OFFSET is an alternative. my formula for the Named Range is:
=$B$1:INDEX($B$1:$B$6,COUNTA($B$1:$B$6)-COUNTA($E$1:$E$6))

i placed that inside the Named Range & called it DRange. lastly, i did the data validation in E1:E6. i did a screenshot on how you should put that in

**Marcol** · 06-22-2013, 07:25 AM

Here is another way, without the errors in the drop-downs

See Sheet1

List 1 in the Lookup sheet is dynamic, add to it as you need
If you delete something in List 1, either sort the list, or move the remaining data up. (Or fill with something else.)

Don't leave blanks

**red61** · 06-22-2013, 08:33 PM

Hi benishiryo,

Thank you very much for your response and your warm welcome.

I have been studying hard to understand the formulas and modify them, but as I have been awake for 19 hours and I've been at work all day I don't mind admitting that I'm struggling. I have entered the formulas as you suggested and so far I can't even get the cells to have the correct validation on them. I can understand how the formulas work though; I think there's a problem with my referencing so I included the file I've been working on, you can see my work so far on Sheet 2.

Yours Gratefully,

red61

**Tony Valko** · 06-22-2013, 10:07 PM

I'm not sure where you want the drop down lists but you can try this on your Sheet1 to see how it works.

Enter this array formula** in B1:

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(COUNTIF(E$1:E$6,A$1:A$6)=0,ROW(A$1:A$6)),ROWS(B$1:B1)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to B6.

Clear the validation that is currently applied to the range E1:E6.

Now, apply this validation

Select the range E1:E6
Data>Validation
Allow: List
Source: =OFFSET(B$1,,,COUNTIF(B$1:B$6,"?*"))
OK out

Try it out!

**red61** · 06-23-2013, 05:35 AM

Hi Tony,

Thanks for your advice. I tried to enter the LOOKUP formula you suggested and I modified it myself as shown in Sheet2:
=LOOKUP("AA$3:AA$22",CHOOSE({1,2},"",INDEX(AA:AA,SMALL(IF(COUNTIF(C$3:C$41,AA$3:AA$22)=0,ROW(AA$3:AA$22)),ROWS(AB$3:AB3)))))

I keep getting the response:
=LOOKUP("AA$3:AA$22",CHOOSE({1,2}))

I don't understand this formula at all. If you could please take a look at Sheet2 you will see what I am tying to achive. It is probably really simple to all the Spreadsheet wizards out there, but to a novice like myself this is really difficult!!

Thanks again,

red61

**red61** · 06-23-2013, 06:10 AM

Hi Marcol,

Thank you very much for your interpretation of my needs and your example was very simple to modify and works!

I am using this sheet to track a game of Killer for the BPL next season. I can send you a copy of the final sheet if you like?

Regards,

red61

**Tony Valko** · 06-23-2013, 09:34 AM

Ok, enter this array formula** on Sheet2 in cell AB3. Don't change anything. Enter it exactly as shown.

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(AA:AA,SMALL(IF(COUNTIF(C$3:C$41,AA$3:AA$22)=0,ROW(AA$3:AA$22)),ROWS(AB$3:AB3)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Then, as the source for the drop down list use:

=OFFSET(AB$3,,,COUNTIF(AB$3:AB$22,"?*"))

I still don't quite understand how you intend to use this because where you seem to want the drop downs there are more cells then available selections in the drop down list?