Excel - Creating Dynamic Lists in Excel using Data Validation and Indirect Functions - Episode 1130
Microsoft Excel Tutorial: Creating Dynamic Lists in Excel using Data Validation and Indirect Functions.
Welcome to the MrExcel netcast, where we dive into all things Excel. In this episode, we'll be tackling a question sent in by Greg about using data validation and indirect functions to create dynamic lists. So let's get started!
Greg's question involves using range names and data validation to create a list of options for users to choose from. He wants to have a second list that changes depending on the first choice made. For example, if the user chooses "Dogs" from the first list, the second list will show breeds of dogs, and if they choose "Cats", the second list will show breeds of cats. But there's a catch - the range names have the word "My" before them, which doesn't match the options in the second list.
To solve this problem, we'll be using the INDIRECT function in combination with data validation. First, we'll set up the data validation for the first list, allowing the user to choose from the range names "MyDogs" and "MyCats". Then, for the second list, we'll use the INDIRECT function to dynamically change the range based on the first choice. This is done by using the formula =INDIRECT("My"&G1), where G1 is the cell containing the first choice.
Now, when the user chooses "Dogs" from the first list, the second list will show breeds of dogs from the "MyDogs" range. And if they choose "Cats", the second list will show breeds of cats from the "MyCats" range. This is a great trick for creating dynamic lists in Excel and can be applied to various scenarios.
I want to thank Greg for sending in this great question and for giving us the opportunity to learn and explore new Excel techniques. And as always, thank you for tuning in to the MrExcel netcast. Don't forget to like, comment, and subscribe for more Excel tips and tricks. See you next time!"
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) Solving a Data Validation Problem
(00:23) Setting Up Data Validation
(00:42) Dynamic Data Validation
(01:04) Alternative Solution
(01:14) Using the INDIRECT Function
(01:36) Testing the Solution
(01:55) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Cats
Collie
Data validation
Dogs
Pivot table
Poodle
Range names
Siamese Hairless
Spaniel
Tabby
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Greg wants to set up dynamic validation, but he has a wrinkle....Once someone chooses from the first data set, he needs to append the word My before that answer to get to the name of the second data set. Episode 1130 shows you how.