Dynamic Drop Down List

D

dhstein

I have a dynamic drop down list. I define a name "VendorList" with this
formula:

=OFFSET(Inventory!$KB$2,0,0,COUNTA(Inventory!$KB:$KB),1)

In my dropdown location - I use =VendorList and it works.

The problem is that there are cell in column KB that evaluate to "" and I
don't want these in the dropdown. These are at the end of the column. So I
tried to define VendorList like this:

=OFFSET(Inventory!$KB$2,0,0,SUMPRODUCT(--(KB2:KB200<>"")),1)

But this doesn't work. I get no items in the dropdown.

Is it that I can't use SUMPRODUCT in this way? Is there another solution?
Thanks for any help on this.
 
T

T. Valko

Define VendorList using *absolute references*:

=OFFSET(Inventory!$KB$2,,,SUMPRODUCT(--($KB$2:$KB$200<>"")))
 
T

T. Valko

Ooops!
=OFFSET(Inventory!$KB$2,,,SUMPRODUCT(--($KB$2:$KB$200<>"")))
Click to expand...

I forgot to include the sheet name in the SUMPRODUCT function.

Should be:

=OFFSET(Inventory!$KB$2,,,SUMPRODUCT(--(Inventory!$KB$2:$KB$200<>"")))
 
D

dhstein

Thanks to you both. I'm sure NBVC's answer is correct, but I changed the
SUMPRODUCT to absolute addressing and that worked perfectly. Thanks Biff.

David
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Named Range 4
Dynamic Range (again) 7
Dependent drop down dynamic range formula, off sheet. 7
Dynamic range names, multiple criteria, sumproduct 1
Drop Down with dynamic named range offsheet source list 14
Changing named Validation list to Dynamic list. 1
Is it possible to create a dynamic range in CONTROL TOOLBOX (form) 7
VLOOKUP the right choice? 2

Top