How can I Implement Dynamic Dependent Data Validation List in Excel

Question

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.

Answer 1

Easy way in 4 simple steps

Step 1 Create your tables:

Step 2 Name your tables: Select the tables and rename them, so that it matches the value in the primary table Table1, eg. Table2 with the header "Cakes" should be named Cake, since that's the value in the primary table Table1.
But how? Click the corner of the table to select it, enter a new label where it says Table2 or similar and press enter. Don't worry if it still says "Table2" etc.
(Note! This is not necessary for the primary table eg. Table1.)

Step 3 Select where you want your first list, go to the data tab and into data validation. Select "list" and enter: =INDIRECT("Table1") in the source window:

Step 4 Select a cell for the second list, that will be dependent on the first list. Return to data validation, choose "list" and enter =INDIRECT(K2) where "K2" is the location of the first list.

DONE

For a third list, dependent on the second list, repeat step 4 and refer to the second list instead. Good luck!

Answer 2

You're just going to have to take my word on the fact that this works, but I've written a (very messy, spaghetti-code) Macro for this exact purpose. Unfortunately, the code is not elegant at all but hey, it works! As they say... "Make it work, make it right, make it fast".

Step 1: The VBA Code

You'll need three things:

1. Chip Pearson's Arrays module --- the actual VBA code is towards the end.

2. Chip Pearson's Sorting module -- actual VBA code also towards the end.

3. My terribly organized macro

To paste these into VBA modules on your workbook, press Alt+F11, find your project on the Project Explorer, right click on the Modules folder and select "Insert Module". Paste the code from each of the 3 links above into separate modules.

While you're on the VBA Editor, open the References dialog box (Alt+T then Enter) and check 'Microsoft Scripting Runtime'.

Step 2: Structuring your data

With that out of the way, what you'll want now is a sheet with your data (called, say, 'Data') as if it were a nicely structured database. Here's how I'd do it:

Notice I've created two more sheets. One called Validation for where the drill-down validation logic takes place, and one called Main for the actual content that matters to the end user.

Step 3: Main sheet

Let's move over to the Data sheet and setup the following structure:

Select cell C2 and name it Snack.Selected. To name a cell, select it and then hit Alt M M D, then type the name in the dialog that appears, just as the picture shows. For now, enter a value such as 'Cake' just so the next step makes more sense.

Go ahead and also name cell C3 'Type.Selected`, but leave it empty for now.

Step 4: Fetching values from our table in the Data sheet

Go to your Validation sheet and set up the following structure:

As you can see on the screenshot, you should select cells B3:B20 and type the following formula, which is where my Macro actually comes into play:

=MultiLookup("Data","Snack",TRUE,TRUE)

Instead of hitting Enter, hit Ctrl+Shift+Enter because this is an Array Formula

If it works as advertised, you should see a list of your snacks followed by some #N/A errors. The errors are expected -- they just mean you have less snacks than you were expecting based on the size of B3:B20.

Explaining the parameters in the formula:

1. Sheet where data is located ("Data")
2. Field you want from that sheet ("Snack")
3. Remove duplicates (i.e. grouped)? (TRUE)
4. Sorted alphabetically? (TRUE)

Ok, that seems like a lot of work for little value, but here comes the grand finale. On cells C3:C20, you'll place the following formula:

=MultiLookup("Data","Type",TRUE,TRUE,"Snack",Snack.Selected)

This should show the available types of Cake, which is the value you entered in the Main sheet in the previous step, remember?

This works by adding two arguments to the formula:

5. Field to filter on ("Snack")
6. Select only values equal to (Snack.Selected)

So when you change your data in Main, the Types column gets updated automatically!

Step 5: Wrapping up validation

To wrap things up, let's finish our data validation by creating the Snack.Choices and Type.Choices names. Please name cells B1 and C1 in Validation respectively. You can also just type on this little box to name a cell:

On cell B1, you need to place the following formula:

="Validation!"&CELL("address",B3)&":"&CELL("address",OFFSET(B$2,COUNTIF(B3:B50,"*"),0))

What this does is build a text reference to Validation!$B$3:$B$6 which is where your snack choices are located. Copy that formula to the right and you're done!

Go back to your Main sheet and let's refer to these cells in the validation rules for Snack and Type.

Select cell C2 and kbd>Alt A V V to create Data Validation. Pick 'List' and set the source to =INDIRECT(Snack.Choices). Notice there are no quotes around Snack.Choices

Do the same on cell C3 but set the source to =INDIRECT(Type.Choices).

We use INDIRECT() because the values of Snack.Choices and Type.Choices are indirect (i.e. text) references to ranges on our workbook.

Now play around with the validation options to see the whole thing in action.

Let me know if you have any questions!