To "Form Board Tables" code module.
Will work when you change any cell in Col.B that have formula in Col.D.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, x
If Intersect(Target, Columns("b")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Intersect(Target, Columns("b"))
If r(, 3).HasFormula Then
r(, 4).Validation.Delete
x = MatchAll(r(, 3), Sheets("xref").Range("a2:b500"), 2)
If UBound(x) > -1 Then
r(, 4).Validation.Add 3, , , Join(x, ",")
Else
r(, 4).Value = ""
End If
End If
Next
Application.EnableEvents = True
End Sub
Function MatchAll(r As Range, rng As Range, ref As Long)
With rng
MatchAll = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & "=trim(" & _
r.Address(, , , True) & ")," & .Columns(ref).Address & "))"), False, 0)
End With
End Function
Bookmarks