Filtered drop down list

[Peter Niepel]

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 productnames 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

 

[Claus Busch]

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 here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "ActiveProducts". Rightclick and download it because
macros are disabled in SkyDrive.


Regards
Claus Busch

 

[Peter Niepel]

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 overto an Excel power user with no dev experience.

I meanwhile found a solution which works here:

Not the most elegant solution but it does the job.

Cheers

Peter