Dependant drop down lists

**Will31** · 11-04-2013, 07:05 AM

Hi All,

I've had a good search on the net and can't find what I am looking for.

I have a large table of B2B contacts with the company name in column B and contact names in column E in sheet 'UK List'. There are multiple contacts at each company and this list is growing all of the time.

I want to set up a drop down that shows only the company contacts for the company selected on sheet 'Account entry' cell B2 so that the rest of the names are not in the list.

Does anyone have any ideas if I can achieve this?

Thanks in advance,

Will

**ben_hensel** · 11-04-2013, 07:12 AM

Dynamic Dependent Drop-down lists at Excel Semipro pretty much says everything I was going to say in a clearer, more illustrated way.

**Will31** · 11-04-2013, 07:41 AM

Hi Ben,

I looked at that page too, the problem is it requires me to set up and maintain a list for each company of which there over 150. I'm hoping there is a way to achieve this without a huge table.

Thanks

Will

**ChemistB** · 11-04-2013, 10:22 AM

I don't know of any direct way to do that from your table. Data Validation and Defined names don't accept Arrayed formulas.

Are you looking for something like this? It uses the same idea but uses formulas to automatically create the dependent lists so you never need to worry about it. Take a look and ask any questions (not sure of your level of expertise). I also included a short event driven macro to clear the "Choose Contact" field when you change the company so you'll need to enable macros for that portion to work.