|
Dynamic Drop Down List
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. |
Dynamic Drop Down List
Try: =OFFSET(Inventory!$KB$2,0,0,COUNTA($KB:$KB)-COUNTIF($KB:$KB,""),1) -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126615 |
Dynamic Drop Down List
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. |
Dynamic Drop Down List
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. |
Dynamic Drop Down List
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. |
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. |
| All times are GMT +1. The time now is 01:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com