Removing Names from a list

[Vehemence]

Hi I am trying to take a list of names and than display them in individual cells by selecting them from a drop box, however I choose each name I want them to be removed from the list. My formula looks like this, but I am sure I am probably way off base.

=IF((C1:C205=C207)," ",0)

Here are the things I am trying to tackle.

A) Create a list of people
B) Have a drop down of that list
C) As I pick names remove them from the list so I cannot choose a name twice.

Please for the love of god help me before I wound my desk

 

[Norie]

How are you creating the dropdown?

Is it on a worksheet or a userform?

The only way I can think of doing what you want would be to use a combobox on a userform with some VBA code.

 

[Zack Barresse]

Hi Vehemence, welcome to the board!!

Sounds like MS MVP Debra Dalgleish's site has just what you need ...

[home]
[data validation]
[unique validation]


HTH

 

[Brian from Maui]

Hi I am trying to take a list of names and than display them in individual cells by selecting them from a drop box, however I choose each name I want them to be removed from the list. My formula looks like this, but I am sure I am probably way off base.

=IF((C1:C205=C207)," ",0)

Here are the things I am trying to tackle.

A) Create a list of people
B) Have a drop down of that list
C) As I pick names remove them from the list so I cannot choose a name twice.

Please for the love of god help me before I wound my desk

See,

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

 

[a7n9]

Hi, I've created this sub,try it,

Before you do it few things you need to do.
1. Create a column with all the names in it.
2. Select a cell where you want to see you list.
3. Keeping the selection, go to data>>Validation>>Validation criteria as List>>Select the source as your list of names.
4. Replace the references in the following code
B4 is the cell where your list is.
C4:C8 is where your names are
and paste this code in the sheet you're working

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
Dim myrange As Range
Set myrange = Range("C4:C8")
Dim c As Object
For Each c In myrange
    If c = Target Then
    c = ""
    c.Select
    Selection.Delete Shift:=xlUp
    Target = ""
    End If
Next c


End If
End Sub

 

[Vehemence]

Man you guys are awesome. In two days I have went from no excel knowledge to making people in the office look like monkeys Heck in about 2 weeks I might even be able to help other people with stuff.