select drop down dynamically from excel

[BMD44]

Hello All,

I am trying to select values dynamically in drop down in 'Sheet' based on the source selection in 'Acct_Map' sheet. But, it doesn't show all values in the drop-down.

Attached the sheet. Can anyone please advise. Thanks for the help.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, x
If Target.Row < 4 Then Exit Sub
If Intersect(Target, Columns("c")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Intersect(Target, Columns("c"))
With r(, 2)
.Validation.Delete: .ClearContents
If r.Value <> "" Then
x = Join(Filter(Sheets("Acct_Map").Evaluate("transpose(if(c1:c10000='" & _
Me.Name & "'!" & r.Address & ",d1:d10000))"), False, 0), ",")
If Len(x) Then .Validation.Add 3, Formula1:=x
'If Len(x) Then .Validation.Add Type:=xlValidateList, Formula1:=x

End If
End With
Next
Application.EnableEvents = True
End Sub