Conditiional drop-down (based on > than value)

[skydivetom]

Experts:

I need your help with creating a conditional name range. Here's what I have thus far.

Cells A1:A10 are saved as name range "Customers". I then use that name range ("Customer") as a drop-menu in another cell.

Some of the 10 customers (naturally, I have more in reality), however, had not made a purchase yet. That is, customer in A3 or A6 or A10 have placed 0 orders. Therefore, I want my drop-down menu to only include the other 7 customers (A1, A2, A4, A5, A7, A8, A9). Once, any (or all) of the other three customers placed an order, I want the drop-down menu to reflect that change and show up to 10 customers.

My question: How can I create this dynamic name range (drop-down menu) where I only include customers where value in B1:B10 is greater than 0?

Thank you,
EEH

 

[jorismoerings]

Hi,

See if this helps. Yellow cells is your basic input, blue is a helper column, purple is the list to be used in the drop-down list.

Book1
	A	B	C	D
1	Customerlist	Orders	Help	Use in Drop Down
2	Customer 1	90	2	Customer 1
3	Customer 2	12	3	Customer 2
4	Customer 3	0		Customer 4
5	Customer 4	23	5	Customer 5
6	Customer 5	75	6	Customer 7
7	Customer 6	0		Customer 8
8	Customer 7	87	8	Customer 9
9	Customer 8	53	9
10	Customer 9	24	10
11	Customer 10	0
Lists

Cell Formulas
Range	Formula
C2	=IF(B2=0,"",ROW())
D2	=IF(ROW(A2)-ROW(A$2)+1>COUNT(C$2:C$11),"",INDEX(A:A,SMALL(C$2:C$11,1+ROW(A2)-ROW(A$2))))

Of course formula's to be copied down to your need.

 

[skydivetom]

jorismoerings -- thank you... yes, that solution works great! I appreciate your feedback.