Exclude a specific value from drop down list

[saurab8]

I have a table where I have staff names. I use these staff name to create down down list using INDIRECT formula and it works perfectly.

Now I want to create a new drop down list, but want to exclude few specific staff name and show rest of the staff names.

I using below formula but I get an error "You may not use reference operators (such as unions, intersections and ranges) or array constants for Data Validation criteria"

=OFFSET((INDIRECT("STAFF_NAME[Name]")),0,0,12,1),OFFSET((INDIRECT("STAFF_NAME[Name]")),13,0,MAX(1,COUNTA(INDIRECT("STAFF_NAME[Name]")))-13,1)

Table name = STAFF_NAME
Column name (within the table) = Name

How to fix the error?

Plus I know my formula only exclude the name if its in 14th position, if there is a way to ensure name can be anywhere in the data it will be excluded will be awesome.

 

[pbornemeier]

I don't know how to do it with a formula, but here is some VBA code to do it:

Option Explicit

Sub UpdateDataValidationList()

    Dim rngCell As Range
    Dim sOmitNames As String
    Dim sDropDownList As String
    Const sDataValidationCellAddress As String = "K20"
    
    sOmitNames = "OmitName1, OmitName2, OmitName3, James Nelson"
    'set rnginput = STAFF_NAME[Name]
    For Each rngCell In Range("staff_name[[#Data],[name]]")
        If InStr(sOmitNames, rngCell.Value) = 0 Then
            sDropDownList = sDropDownList & "," & rngCell.Value
        End If
    Next
    If Len(sDropDownList) > 1 Then sDropDownList = Mid(sDropDownList, 2)
    With Range(sDataValidationCellAddress).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=sDropDownList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub