Tuesday, September 26th, 2017 / by Excel Champ
Remember that complicated home renovation and/or furniture assembly project you convinced yourself to work on independent of expert help?
When tasked with complicated endeavors, don’t be an average human being. Be an exceptional human being and ensure you’re set up with expert knowledge and expert help, especially if you’re attempting to create dynamically filtered dropdown lists within Microsoft Excel.
Dynamically filtered dropdown lists are dropdown lists that automatically filter based on the selections in other dropdown lists. This functionality can be extremely helpful when attempting to isolate subsets of data from large data sets that are characterized in multiple ways.
For example, imagine you have a list of thousands of food items and are tasked with creating a list of all the food items that are considered fruits and are red in color. Dynamically filtered dropdown functionality would allow you to select a food type and color categorization (in any order) to isolate a list of food items that meet the selected specifications.
To get started with creating dynamically filterable dropdown lists, data needs to be structured in a way that allows Excel to easily identify and characterize each item. A flat structure usually works well for this.
Once data has been structured, Excel pivot table functionality can be leveraged to generate unique filters and lists of each characterization/parameter.
After unique lists have been generated, dynamic named ranges should be set up to encompass each unique list.
To set up the dropdown lists, data validation can be added to a cell and linked to the dynamic named ranges to display a dropdown list of items.
The last piece of this puzzle is event-driven and VBA functionality in Excel. An Excel VBA procedure can be set up to capture changes in dropdown selections and automatically apply parameter selections to the pivot table filters. Once parameter selections have been applied to the pivot table filters, the unique list of items will update, pass through the dynamic named ranges, and display in the data validation dropdowns.
If dynamic dropdown list functionality would be beneficial to your Excel processes, contact us for a free consultation by calling 1-800-682-0882 or submitting an inquiry at ExcelHelp.com. After all, the latest origami fads and avocado toast recipes deserve your full attention.
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.