Hi Excel experts,

Excel Version: 2010

I need some help please. I need to use a drop down list which is filtered based on a flag. As an example:

The products table has two columns, Product and Active. Product has product names and active has a flag (1 or 0) to activate/deactivate a product. On another worksheet - lets call it order items - I need to use a drop down field in a column which provides all active products as values.

What I did so far is, I added a third column in the products sheet called it "Active products" and use this as the data source for the drop down list. This has either a product name if the Active column is 1 or is blank. It works ok-ish with the one problem that the blanks do show in the drop down list. Not nice! I also used a name range for this column because I read somewhere that using a name range in the list property of a drop down removes empty cells. This doesn't work.

Any ideas how I could filter the list of a drop down based on another column's value?

Thank you for your time.

Peter

Hi Peter,

Am Wed, 12 Jun 2013 21:35:21 -0700 (PDT) schrieb Peter Niepel:

The products table has two columns, Product and Active. Product has product names and active has a flag (1 or 0) to activate/deactivate a product. On another worksheet - lets call it order items - I need to use a drop down field in a column which provides all active products as values.

What I did so far is, I added a third column in the products sheet called it "Active products" and use this as the data source for the drop down list. This has either a product name if the Active column is 1 or is blank. It works ok-ish with the one problem that the blanks do show in the drop down list. Not nice! I also used a name range for this column because I read somewhere that using a name range in the list property of a drop down removes empty cells. This doesn't work.

it is easy to handle with VBA. Look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "ActiveProducts". Rightclick and download it because
macros are disabled in SkyDrive.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you for your reply. Will definitely have a look. I forgot to mention that my preferred solution would be a non-VBA one. I need to hand this over to an Excel power user with no dev experience.

I meanwhile found a solution which works he http://www.youtube.com/watch?v=6PcF04bTSOM Not the most elegant solution but it does the job.

Cheers

Peter

On Thursday, June 13, 2013 8:05:42 PM UTC+12, Claus Busch wrote:
Hi Peter,



Am Wed, 12 Jun 2013 21:35:21 -0700 (PDT) schrieb Peter Niepel:



The products table has two columns, Product and Active. Product has product names and active has a flag (1 or 0) to activate/deactivate a product. On another worksheet - lets call it order items - I need to use a drop down field in a column which provides all active products as values.



What I did so far is, I added a third column in the products sheet called it "Active products" and use this as the data source for the drop down list. This has either a product name if the Active column is 1 or is blank. It works ok-ish with the one problem that the blanks do show in the drop down list. Not nice! I also used a name range for this column because I read somewhere that using a name range in the list property of a drop down removes empty cells. This doesn't work.



it is easy to handle with VBA. Look he

https://skydrive.live.com/#cid=9378A...121822A3%21326

for the workbook "ActiveProducts". Rightclick and download it because

macros are disabled in SkyDrive.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2