Create dropdown list only if ColB is greater than 0

creative999

Board Regular
Joined
Jul 7, 2021
Messages
112
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
Solution
VBA Code:
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.
 
Upvote 0
Maybe this. Not tested.
VBA Code:
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
 
Upvote 0
Maybe this. Not tested.
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,527
Messages
6,179,331
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top