Remove selected items from drop down list

Question

I have 5 dropdown lists with following values A,B,C,D,E if i select 'B' in first dropdown, remaining dropdowns have A,C,D,E if i select 'C' in first dropdown, remaining dropdowns have A,B,D,E vise versa.

Does anybody know how to do it?

Answer 1

Say we want the initial drop-downs on the five cells A1 through E1 to be:

Alpha,Beta,Gamma,Delta,Epsilon

First run this macro:

Sub InternalString()
    Dim MyCells As Range, FullString As String
    Dim r As Range
    Set MyCells = Range("A1:E1")
    FullString = "Alpha,Beta,Gamma,Delta,Epsilon"
    Application.EnableEvents = False

        With MyCells.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=FullString
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Application.EnableEvents = True
End Sub

Public Function RemoveItem(st As String, drop As String) As String
    RemoveItem = Replace(Replace(st, drop, ""), ",,", ",")
End Function

Since we want our pick for A1 to be removed as an option for cells B1 through E1, put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A1 As Range, v As String, PartString As String
    Dim FullString As String
    FullString = "Alpha,Beta,Gamma,Delta,Epsilon"
    Dim rng As Range
    Set A1 = Range("A1")
    Set rng = Range("B1:E1")

    If Intersect(A1, Target) Is Nothing Then Exit Sub

    v = A1.Value
    PartString = RemoveItem(FullString, v)
    With rng.Validation
        .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=PartString
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
    End With

End Sub

This macro will detect our choice for A1 and remove the item as a choice for B1 through E1.

Answer 2

If you want to do this directly on the sheet, then you could use a combination of the tablefunctions "IF", "ISNUMBER" and "SEARCH" in your data validation list. You need different data validation lists for every drop down list. The first data validation list has only values A,B,C,D,E, but the 2. list has functions:
=IF(ISNUMBER(SEARCH(A1; A$7)); ""; "A")
=IF(ISNUMBER(SEARCH(A2; A$7)); ""; "B")
=IF(ISNUMBER(SEARCH(A3; A$7)); ""; "C")
=IF(ISNUMBER(SEARCH(A4; A$7)); ""; "D")
=IF(ISNUMBER(SEARCH(A5; A$7)); ""; "E")

where A7 is the cell with the first dropdownlist, the 3. list has functions

=IF(ISNUMBER(SEARCH(B1; B$7)); ""; "A")
=IF(ISNUMBER(SEARCH(B2; B$7)); ""; "B")
=IF(ISNUMBER(SEARCH(B3; B$7)); ""; "C")
=IF(ISNUMBER(SEARCH(B4; B$7)); ""; "D")
=IF(ISNUMBER(SEARCH(B5; B$7)); ""; "E")

where B7 is the cell with the second dropdownlist, and so on with the 4. and 5. list

some pictures. sorry the dialogbox is in german, but I guess you know how to make a data validation drop down list.