Dependent drop-down lists in a table range

[Iziss]

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
Coffee	Coffee Latte
Coffee	Latte Machiatto
Coffee	Black
Coffee	Espresso
Tea	Blueberry
Tea	English Tea
Vodka	Sec
Vodka	Double Sec
Vodka	Triple 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 beverage	Name	Number of drinks	Date
Coffee	Black	John	5	20-01-2017
Tea	English Tea	Maria	3	15-01-2017
Tea	Blueberry	Gina	6	13-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]).

 

[MickG]

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.

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

 

[Iziss]

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