Drop Down Menu Problem

leonhart

New Member
Joined
Nov 3, 2005
Messages
1
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top