下面的代码，是通过VBA来设置数据有效性的例子，希望对你有帮助。
If Target.Count > 2 Then Exit Sub
If Target.Column <> 3 Or Target.Row <3 Then Exit Sub
Dim aa, Arr, i&
Target.Offset(0, -1) = ""
Arr = Sheet1.[a1].CurrentRegion
For i = 3 To UBound(Arr)
If Arr(i, 2) = Target.Value Then
aa = aa & Arr(i, 1) & ","
End If
Next
If aa = "" Then MsgBox "没有找到编号": Exit Sub
aa = Left(aa, Len(aa) - 1)
With Target.Offset(0, -1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=aa
End With