Re: Function to Ignore duplicates in a data validation list

Discussion in 'Excel Worksheets' started by Debra Dalgleish, Apr 2, 2004.

  1. To do this dynamically, you could use an event procedure. For example,
    if your list of cost centres is in C2:C20, the following code will copy
    the unique values to a sheet named Lists, sort the list, and name it,
    when a cell in column C is selected.

    The code should be pasted onto the module for the sheet with the data
    validation. Right-click the sheet tab, choose View Code, and paste the
    code onto the code module, where the cursor is flashing.

    '==================================
    Private Sub Worksheet_SelectionChange _
    (ByVal Target As Range)
    Dim r As Long
    Dim wsL As Worksheet
    Set wsL = Worksheets("Lists")
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 3 And Target.Row > 2 Then
    wsL.Range("A1").CurrentRegion.ClearContents
    Range(Cells(2, 3), Cells(Target.Row - 1, 3)) _
    .AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=wsL.Range("A1"), Unique:=True
    r = wsL.Cells(Rows.Count, 1).End(xlUp).Row
    wsL.Range(wsL.Cells(1, 1), wsL.Cells(r, 1)).Sort _
    Key1:=wsL.Range("A1"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    ActiveWorkbook.Names.Add Name:="DataValList", _
    RefersToR1C1:="=Lists!R1C1:R" & r & "C1"
    With Target.Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, _
    Formula1:="=DataValList"
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowError = False
    End With
    End If
    End Sub
    '===========================

    Kevin Wickersheim wrote:
    > I have a sheet where the user will enter peoples names and cost centers. Below this I have set a cell with Data>Validation>list and the ranges is the cost centers from the people that where entered. Is there a way to get this drop down list to ignore duplicate cost center?



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html
     
    Debra Dalgleish, Apr 2, 2004
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Patrick G

    list validation using list validation...

    Patrick G, Dec 21, 2004, in forum: Excel Worksheets
    Replies:
    1
    Views:
    115
    RagDyer
    Dec 21, 2004
  2. Ricky

    Ignore Blanks in Data Validation

    Ricky, Jul 1, 2005, in forum: Excel Worksheets
    Replies:
    9
    Views:
    100
    Debra Dalgleish
    Jul 7, 2005
  3. vane0326

    LOOKUP multiple results but ignore duplicates.

    vane0326, May 31, 2006, in forum: Excel Worksheets
    Replies:
    10
    Views:
    73
  4. ilia
    Replies:
    0
    Views:
    136
  5. David

    Ignore Blank in Validation not working

    David, Jul 1, 2007, in forum: Excel Worksheets
    Replies:
    4
    Views:
    53
    Bob Phillips
    Jul 1, 2007
  6. D Lu
    Replies:
    4
    Views:
    132
  7. Nuclear
    Replies:
    2
    Views:
    155
    M Kan
    Jul 29, 2008
  8. AKrobbins
    Replies:
    2
    Views:
    303
    Gord Dibben
    Jun 21, 2011
Loading...