Removing Names from a list

Vehemence

New Member
Joined
Jan 26, 2005
Messages
2
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 :)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Vehemence said:
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

Well-known Member
Joined
Sep 15, 2004
Messages
696
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

Code:
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

New Member
Joined
Jan 26, 2005
Messages
2
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.
 

Forum statistics

Threads
1,148,216
Messages
5,745,431
Members
423,951
Latest member
peggrif

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
Top