| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Date Drop-down List | Excel Worksheet Functions | |||
| Drop down list dependant on previous drop down list | Excel Discussion (Misc queries) | |||
| How to feed dynamic information to a drop down List (Validation Feature)? | Excel Worksheet Functions | |||
| Dynamic Drop Down List | Excel Discussion (Misc queries) | |||
| multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Linear Mode
