Excel – How tomplement Dynamic Dependent Data Validation List in Excel

Sources on the web show how to implement dependent data validation lists but all I have come across are static and do not update automatically.

I believe whoever might be able to answer this question already knows how data validation lists work to a professional degree but for the purposes of learners like myself I will give a brief description (I suggest further reading on google and youtube videos).

Data validation lists enable you to create a defined data structure in your workbook. They are more intended for a user interaction basis. If you wanted a workbook user to only insert a set of given data for example Donuts, Cakes, Muffins and Crumpets you could create a list using a named range say "Snacks" or could create a table and name your table as "Snacks". Where the given name is one word (i.e. no spaces exist in the given name)

The downside to using lists is that if we had to add another snack to the list called Croissants we then we would need to go redefine the originally created lists. Tables bypass this inefficiency in the sense that you can easily add a new row to a table by appending it from the last row or writing the in succeeding row following the current table row and hitting enter.

Now for data validation, I recommend visiting the link below for a detailed expose as an explanation makes this question too lengthy. http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right/

A dependent validation list is thus a list which is based on user selected value from a previous validation list, for instance, Donut types include iced ring, chocolate glazed, Jam and Custard donuts. Muffin types include banana, blueberry, and chocolate. Cake types include red velvet, Carrot, and Coconut. Finally, Crumpets types include English, Scottish, and Pikelets.

Thus if a cell has a drop down of the main category list how do you make a dynamic dependent validation list based on the sub-categories.

By dynamic dependent validation list, I mean all the column lists are tables as shown below.

Now the question is how do I implement an adjacent cell for example if cell C13 has a validation list for the snacks table how to I make cell D13 a validation list based on user selected input of C13 (i.e. if C13 is a selected as Donuts then the possible selection of D13's validation list will be the column data of the Donuts table).

Please, it is important to note that the original validation list in C13 would have been created using the INDIRECT function since it is a table and not a named range.