Create dropdown list only if ColB is greater than 0

[creative999]

Hi
I have a list of values, no duplicates in ColA (salesperson) and the corresponding $ values in ColB.

I need to create a dropdown list for ColA values but only show the values if the corresponding column value is greater than 0.

Any suggestions?

 

[MrRajKumar]

In 365, use FILTER formula to get col A values in different range, then use that range in your drop down list.

=FILTER(A2:A100,B2:B100>0,"No record")

This will give value from A2:A100 if the values in B2:B100 are >0

 

[Jaafar Tribak]

Sub test()
    Dim v As Variant
    v = Application.Transpose(Evaluate("=IF(Sheet1!B2:B100>0,Sheet1!A2:A100,""|"")"))
    v = Filter(v, "|", False)
    Sheet1.DropDowns(1).List = v
End Sub

That's for versions prior to 365. It should be added to the sheet calculate or\and change events.

Question:
How do we edit the Evaluate ranges above so it covers cells from A2 to last used cell in column A ? (Instead of hardcoding it A2:A100 )

I have tried concatenating the ranges with the last used row but I couldn't make it work.

Thanks.

 

[Cubist]

Maybe this. Not tested.

Sub test()
    Dim lastRow As Long
    Dim rangeString As String
    Dim v As Variant
    
    lastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    rangeString = "Sheet1!A2:A" & lastRow
    v = Application.Transpose(Evaluate("=IF(Sheet1!B2:B" & lastRow & ">0," & rangeString & ",""|"")"))
    v = Filter(v, "|", False)
    Sheet1.DropDowns(1).List = v
End Sub

 

[Jaafar Tribak]

Maybe this. Not tested.

Sub test()
    Dim lastRow As Long
    Dim rangeString As String
    Dim v As Variant
   
    lastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    rangeString = "Sheet1!A2:A" & lastRow
    v = Application.Transpose(Evaluate("=IF(Sheet1!B2:B" & lastRow & ">0," & rangeString & ",""|"")"))
    v = Filter(v, "|", False)
    Sheet1.DropDowns(1).List = v
End Sub

That worked beautifully! I just couldn't get the double quotes right.

Thanks a lot Cubist.