Conditional searchable dropdown list

[nihad]

Hi all

I have 2 searchable dropdown list on column B and C I'm using Filter Function

=TRANSPOSE(SORT(UNIQUE(FILTER(LocationList[Name],ISNUMBER(SEARCH('Adj Sheet'!B5,LocationList[Name],1)),"location not found"))))

Is there away to add a condition to the C dropdown list to not show any value unless a value from dropdown B is selection?

Kind regards

Adjustment Sheet.xlsx
	A	B	C
4	Line	To Location	Category
5	1	food	Food Purchases 5000
Adj Sheet

Cells with Data Validation
Cell	Allow	Criteria
B5:B66	List	='To Loc'!E2#
C5:C66	List	='To Nom'!E2#

 

[diddi]

this may help you out

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B5:B66")) Is Nothing Then
        If Target.Value <> "" Then
            'Dropdown and error message on cells 2 and 3 columns left of "Make a selection" will be enabled
            With Cells(Target.Row, Target.Column + 1).Validation
                .InCellDropdown = True
                .ShowError = True
            End With
        Else
             With Cells(Target.Row, Target.Column + 1).Validation
                .InCellDropdown = False
                .ShowError = False
            End With
        End If
    End If
End Sub

 

[nihad]

this may help you out

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B5:B66")) Is Nothing Then
        If Target.Value <> "" Then
            'Dropdown and error message on cells 2 and 3 columns left of "Make a selection" will be enabled
            With Cells(Target.Row, Target.Column + 1).Validation
                .InCellDropdown = True
                .ShowError = True
            End With
        Else
             With Cells(Target.Row, Target.Column + 1).Validation
                .InCellDropdown = False
                .ShowError = False
            End With
        End If
    End If
End Sub

Hi Thanks for this, Is it possible to do it without VBA?