Remove items from dropdown list when item value = 0

**technolog** · 11-21-2017, 07:51 AM

Hello,

I would like to remove items from dropdown list when value that is attributed to that item equals 0

Label Amount
Book1 5
Book2 2
Book3 0

In this example I would like to remove in dropdown list (data validation) book 3, because value for amount is 0.

Can you please help me with the correct formula?

Thank you.

**Sam Capricci** · 11-21-2017, 08:11 AM

I don't think you can do that with a formula without a helper column. you could use =IF(B2>0,A2,"") then use copy paste special values and take the values in C and past them over the values in A. You could use sort for column B descending then delete all those at the bottom that have a 0. I think the only other way is using VBA and someone might post a formula for that. (I'm assuming that Book1 is in column A and the amount is in column B)

**modytrane** · 11-21-2017, 09:57 AM

see attached example. Does that help? If not, attach a sample file so we can help you with solution. It may require VBA if helper column isn't acceptable.