Drop Down Menu Problem

[leonhart]

Hello Guys,

I desperately need help to solve a drop down menu problem in MS Excel. My situation is this, I want to be able to select a list of values in a drop down menu. But after I selected that particular value, I want to be able to eliminate that value in the drop down menu choices when I use the drop down menu in the next cell. Does anyone know how to do this?

Thank you so much,

leonhart.

 

[acw]

Hi

One way

Sheet1 Column A will contain the DataValidation dropdown lists.
Sheet2 will contain the full list of names available:
In Sheet2:
A1:A13 - list of names including a heading in A1
B2:B13: =COUNTIF(Sheet1!A:A,Sheet2!A2)
C2: Array Enter (ctrl, Shift, Enter)
=INDEX(A:A,SMALL(IF($B$2:$B$13=0,ROW($B$2:$B$13),65536),ROW()-1))
Copy and paste down to C13
Create a defined name (Namess) on sheet with the formula
=OFFSET(Sheet2!$C$2,0,0,COUNTA(Sheet2!$C:$C)-COUNTIF(Sheet2!$C:$C,0)-1,1)

Now go to sheet1, range A2. Data Validation, Allow: List, Source:=Namess
Copy down as required.

This will give you a list of available names. When one is selected, then it will not be available for subsequent selection. Change a previously selected item and it will again become available.

HTH

Tony

In sheet2, A1