Dependent drop-down lists in a table range

Iziss

New Member
Joined
Mar 21, 2017
Messages
2
Hello guys,

I am trying to create a dependent drop-down list in a table range that is dependent on what the user chooses in the first column. The data for the drop down lists are stored in another table on another sheet.

Imagine that the table below is formatted as a table range (Formatted as table - not sure how to call it, hence, table range). This is my example source for the table where the inputs will be. In another sheet i have the unique value validation for the Beverages, done through a VBA code, which updates the list every time new beverages are added to this table, so i can use that as a dynamic range for the first column drop down validation.

Beverages
Types of beverages
CoffeeCoffee Latte
CoffeeLatte Machiatto
CoffeeBlack
CoffeeEspresso
TeaBlueberry
TeaEnglish Tea
VodkaSec
VodkaDouble Sec
VodkaTriple Sec

<tbody>
</tbody>
















Then, on the input sheet i have another table range, where people should start adding lines, one at a time, first choosing their beverage, then, based on that selection, in the second column, from a dependent list on the first choice, only the choice available to that type of beverage. The last three columns are to be manually inputted by the user.

Beverage
Type of beverageNameNumber of drinksDate
CoffeeBlackJohn520-01-2017
TeaEnglish TeaMaria315-01-2017
TeaBlueberryGina613-01-2017

<tbody>
</tbody>

The validation for the first two columns i believe should be added indefinitely (or as much Excel allows). I have searched all over the internet but dependent drop down lists are only shown as one time cell selection for each of the two lists. And i need them to repeat for every new line added to the table. I assume this can be made simply by selecting all the cells from a column, going beyond the last table row when assigning data validation, but what i cannot figure out is how to point to the same cell in a row, on the second column (Type of beverage) in the formula field when selecting list.

I am using Office 2013.

Do you guys have any idea how i can tackle this? With Indirect or Offset or something that works. Plus that i have figured out that the formula field in data validation doesn't recognize table ranges (Table1[Beverages]).
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
The code is based on your database beingin sheet1 columns "A & B".
Place this code in the sheet module for yourResultssheet with expected validation list in columns "A & B",
NB:-these vaildation list will be added by the code.
Clickin column "A" for a validation list to appear in selected cell.
Selecta value in column "A" Val List, for a validation list to appear inthe colum "B" cell.
Code:
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, txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
    [COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
      [COLOR=navy]If[/COLOR] Target.Count = 1 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Target.Column = 1 [COLOR=navy]Then[/COLOR]
            [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng: Dic(Dn.Value) = "": [COLOR=navy]Next[/COLOR]
                [COLOR=navy]With[/COLOR] Target.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]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] ray, txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
    [COLOR=navy]Set[/COLOR] Rng = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
  [COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
     [COLOR=navy]If[/COLOR] Target.Column = 1 [COLOR=navy]Then[/COLOR]
           [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
                [COLOR=navy]If[/COLOR] Dn.Offset(, -1).Value = Target [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
                        Dic(Dn.Value) = ""
                        nStr = nStr & IIf(nStr = "", Dn.Value, "," & Dn.Value)
                    [COLOR=navy]End[/COLOR] If
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]Next[/COLOR]
            [COLOR=navy]If[/COLOR] nStr <> "" [COLOR=navy]Then[/COLOR]
                [COLOR=navy]With[/COLOR] Target.Offset(, 1).Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=nStr
                [COLOR=navy]End[/COLOR] With
             [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR][/FONT][/COLOR]

Regards Mick
 
Last edited:

Iziss

New Member
Joined
Mar 21, 2017
Messages
2
Hello MickG

Thank you oh so much for this! It worked perfectly! I didn't use the first part of the code cause i had already the validation through a dynamic range from another sheet, but the second part worked as a charm. The only thing i need to make sure, is that the dependent drop-down categories do not have "," otherwise the drop-down will split the cell into two or as many parts are broken down by the comma. Otherwise it was perfect! THANK YOU!

Have an amazing day,

BR/
Iziss
 

Watch MrExcel Video

Forum statistics

Threads
1,123,134
Messages
5,599,914
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top