Dynamic Drop Down List
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"dhstein" wrote in message
...
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
"T. Valko" wrote:
Ooops!
=OFFSET(Inventory!$KB$2,,,SUMPRODUCT(--($KB$2:$KB$200<"")))
I forgot to include the sheet name in the SUMPRODUCT function.
Should be:
=OFFSET(Inventory!$KB$2,,,SUMPRODUCT(--(Inventory!$KB$2:$KB$200<"")))
--
Biff
Microsoft Excel MVP
"T. Valko" wrote in message
...
Define VendorList using *absolute references*:
=OFFSET(Inventory!$KB$2,,,SUMPRODUCT(--($KB$2:$KB$200<"")))
--
Biff
Microsoft Excel MVP
"dhstein" wrote in message
...
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.
|