To improve the quality of data and reduce the likelihood of errors dropdowns in Excel can be a good choice. Making these dynamic, cascading, and automatically reset takes them to another level and will amaze your boss (hopefully!).
The short video below shows how they work. Note that when you select a product from the first dropdown the list of options in the second dropdown also changes. The number of options also differs by product. Finally, if a new product is selected the options dropdown resets. Complicated to explain, but much easier to use.
The major steps in setting up Dynamic Dropdowns in Excel
First, the majority of the dropdown setup does not involve any Excel VBA. The only VBA I use is to reset the options list to 'Select Option' or 'Select Product First' when the user selects a new product.
Setting up the dropdowns themselves involves using the Data Validation functionality, a Table of Information, some named ranges, and clever use of INDEX and MATCH functions.
The table of information looks like this:
and the named ranges and their contents look like:
Finally, the VBA to automatically reset the options when a new product is selected looks like this:
The resulting dropdowns and how they work are demonstrated in the short video above. This simple example demonstrates that you can have significant control over user actions. Dynamic dropdowns in Excel also make things easy for users. Finally using dropdowns, especially when dynamic, can greatly increase quality. Quality of data and the quality of your Excel Workbooks.
If you're after step-by-step instructions on how to set something like this up, take a look at this great post by Svetlana Cheusheva on ablebits.com.
If you'd like a copy of my simple example just let me know in the comments below or contact me. Enjoy.
Just what I was looking for. Could I grab the file please.
Glad to hear Grant. File on the way.
The dynamic piece is great. Can I get the file. Thank you
Done.