Dynamic Drop-Down list

[nymyth]

Good Afternoon all,

I am in need of assistance. I am trying to create a dynamic drop-down list based on another drop-down list.

I have hundreds of rows of distributors in Column B and the markets they belong to in Column A. I can put the list of distributors into the drop-down, but what i would like to do is have one drop down give me a list of markets, and from that Market Selection I want the second drop-down to show me the distributors that fall in that market. Is this possible? Thanks in advance.


[TABLE="width: 397"]
<tbody>[TR]
[TD]Mid-South[/TD]
[TD]Triangle Wholesalers Inc., GA[/TD]
[/TR]
[TR]
[TD]Mid-South[/TD]
[TD]Beverage South of Augusta, GA[/TD]
[/TR]
[TR]
[TD]Mid-South[/TD]
[TD]Coastal Beverage Co. Inc. (Wlmgtn), NC[/TD]
[/TR]
[TR]
[TD]Mid-South[/TD]
[TD]Healy Wholesale Co. Inc., NC[/TD]
[/TR]
[TR]
[TD]Mid-South[/TD]
[TD]Ward Dist. Co. Inc., NC[/TD]
[/TR]
[TR]
[TD]Mid-South[/TD]
[TD]Mims Distributing Co. Inc., NC[/TD]
[/TR]
[TR]
[TD]Mid-South[/TD]
[TD]Long Beverage, NC[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]Gold Coast Beverage L.L.C., FL[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]Double Eagle Dist Inc., FL[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]Eagle Brands Sales - Miami, FL[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]Stephens Distributing Co., FL[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]Brown Distributing Co of West Palm, FL[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]Florida Distributing Co., FL[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]Wayne Densch Inc., FL[/TD]
[/TR]
[TR]
[TD]Florida Gulf[/TD]
[TD]S.R. Perrott Inc., FL[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]Carroll Distributing Co. Inc., FL[/TD]
[/TR]
[TR]
[TD]Florida Atlantic[/TD]
[TD]City Beverages Ltd. Partnership, FL[/TD]
[/TR]
[TR]
[TD]Chesapeake[/TD]
[TD]Premium Distributors of VA LLC, VA[/TD]
[/TR]
[TR]
[TD]Chesapeake[/TD]
[TD]Chesbay Distributing L.L.C., VA[/TD]
[/TR]
[TR]
[TD]Chesapeake[/TD]
[TD]Brown Distributing Company, VA[/TD]
[/TR]
[TR]
[TD]Chesapeake[/TD]
[TD]Tri-Cities Beverage Corp., VA[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
https://www.excel-easy.com/examples/dependent-drop-down-lists.html

https://excelchamps.com/blog/dependent-drop-down-list-in-excel/

 

[nymyth]

Thanks, i've tried this, but I keep getting an error. It probably has to do with the fact that column A has multiple rows with the same name.

 

[TWriter1]

What kind of an error do you get? It is OK that you have multiple rows with the same name in column A.

 

[MickG]

Assuming your data sheet is sheet 2, columns "A & B. then try this for results in sheet1 cells "A1 & B1.
Paste the whole code into sheet 1 code module.

Option Explicit
[COLOR=navy]Dim[/COLOR] dic [COLOR=navy]As[/COLOR] Object

Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] ray(), Q [COLOR=navy]As[/COLOR] Variant, nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
[COLOR=navy]If[/COLOR] dic [COLOR=navy]Is[/COLOR] Nothing or [a1] = "" [COLOR=navy]Then[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet2")
    [COLOR=navy]Set[/COLOR] Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
    [COLOR=navy]Set[/COLOR] dic = CreateObject("scripting.dictionary")
        dic.CompareMode = vbTextCompare
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
            [COLOR=navy]If[/COLOR] Not dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
                ReDim Preserve ray(1)
                   nStr = Replace(Dn.Offset(, 1), ",", "")
                    ray(1) = nStr
                    dic.Add (Dn.Value), ray
            [COLOR=navy]Else[/COLOR]
                 Q = dic(Dn.Value)
                  ReDim Preserve Q(UBound(Q) + 1)
                     nStr = Replace(Dn.Offset(, 1), ",", "")
                    Q(UBound(Q)) = nStr
                dic(Dn.Value) = Q
             [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR]

[COLOR=navy]With[/COLOR] Range("A1").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(dic.keys, ",")
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)

[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]With[/COLOR] Target.Offset(, 1).Validation
        .Delete
        .Parent.ClearContents
        .Add Type:=xlValidateList, Formula1:=Join(dic(Target.Value), ",")
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[nymyth]

Magic...this is amazing, thanks. I'll go work to dissect this.

 

[MickG]

You're welcome

 

[nymyth]

Hey Mick,

I have made some tweaks to the formula, but I am getting stumped when the second selection is a merged cell. I know we really shouldn't use merged cells and it's better to use 'center across selection', but this puts my dropdown button in the middle. Is there any chance the last part of this code can be adjusted to show the list in a merged cell?

Thanks.