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