Question on dynamic multiple dependent lists

[Tietjen27]

Does anyone know how to make dependent drop down lists using larger datasets? I understand how to make a dependent drop down list using named ranges, however this method doesn’t seem as practical for what I’m trying to accomplish, as I would rather not make 100+ named ranges and have to add to it each month. Also, if there is a way to make them into a unique list that is organized. I've used the index function (similar to what is used in
Create dependent drop down lists containing unique distinct values in excel | Get Digital Help - Microsoft Excel resource
) to do it, however, because I'm using 1,000+ lines about 50+ uniques in a single column, it tends to lock down excel, as it is trying to process it.
</SPAN>
I receive a monthly data dump that has several columns and I want to be able to drill through the data using drop down lists. </SPAN>
[TABLE="width: 211"]
<TBODY>[TR]
[TD][TABLE="class: grid, width: 100"]
<TBODY>[TR]
[TD]Column 1
[/TD]
[TD]Column 2
[/TD]
[TD]Column 3
[/TD]
[/TR]
[TR]
[TD]Company A
[/TD]
[TD]Location 1
[/TD]
[TD]Product A
[/TD]
[/TR]
[TR]
[TD]Company B
[/TD]
[TD]Location 2
[/TD]
[TD]Product C
[/TD]
[/TR]
[TR]
[TD]Company C
[/TD]
[TD]Location 3
[/TD]
[TD]Product C
[/TD]
[/TR]
[TR]
[TD]Company C
[/TD]
[TD]Location 4
[/TD]
[TD]Product A
[/TD]
[/TR]
[TR]
[TD]Company C
[/TD]
[TD]Location 4
[/TD]
[TD]Product B
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD]
[/TD]
[/TR]
</TBODY>[/TABLE]
Column 1: Each company can appear on the list multiple times, as many times as a product was shipped to them </SPAN>

Column 2: Each company could have multiple locations, as some companies may purchase a product but want it shipped to a different location</SPAN>

Column 3: Each location can have multiple products shipped to any location</SPAN>

I’m really looking for a way to do a dependent list but since the list can grow or shrink easily every month, I need to have them dynamic, as next month, Company A might not have any products, however, we might add a company D with several new locations and they might purchase multiple new products that we didn’t have the month before.
</SPAN>
Similar to the Dependent Drop Down List add-in at the bottom of the link: without having to install an add-in </SPAN>
http://www.get-digital-help.com/2010/07/17/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/</SPAN>

[TABLE="width: 211"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanks for any advice</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Biz]

It's possible to do this in VBA too. Let me know if you are interested in VBA. Pls provide bit more sample data.

 

[Tietjen27]

I wanted to stay away from using VBA on this, as I didn't want to send this to someone else and have them break it. But if that's the only way to do it, I'll just have to be careful how to write the VBA to do it.

Thanks

 

[Biz]

The methods below without vba.

Excel Data Validation -- Dependent Lists

Excel Data Validation -- Dependent Lists With INDEX

I use usually method2 but vba it's so fast. Deleting range names.

Choice is yours mate.