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.

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

<tbody>
</tbody>

 

[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.