Setting up data validation lists, where the second list is dependent upon the answer given in the first dropdown box. Episode 383 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
We've been talking about using Data Validation for the past 4 or 5 netcasts, and today I have a really cool trick that kind of takes data validation to the next level.
What if we have 2 drop-down boxes, and the second drop-down box is dependent on the first one?
So here we have a drop-down box where someone can choose a subject: Reading, Science, Math, or Geography.
And then once they choose that subject, the next drop-down box has to, kind of, dynamically have the concentrations within that subject.
Now to set this up, I'm going to go back to a back-sheet, where I've hidden all of my valid lists.
So I have a list here of the Subjects, and then for each subject I have a second list, and I've assigned that list a name.
You can say that this list is called Reading, and this list is called Science, and then there's one for Math and Geography.
Now here's how to make it work, back on the first sheet, in the second drop-down, I'm going to go to Data Validation.
And when it asks me for where my list is, I'm going to put a formula in, and my formula =INDIRECT.
INDIRECT says “Take whatever comes in the parentheses and treat it as a cell value.” And I’m going to say “The INDRECT of the answer for D2.” Click OK, and now the answer for D2 in this case is Math.
The INDIRECT says “Hey, go find the address called Math!” Which in this case is a named range.
So let me show you how it works, if we come here and change Math to be, let's say Reading, then when we come to D4, the drop-down list will get its values from the named range called Reading.
If we go back to the original drop-down and change to Geography, then this drop-down will dynamically get its list from the range called Geography.
Very cool use to create, let's call it Dependent Validation list.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
We've been talking about using Data Validation for the past 4 or 5 netcasts, and today I have a really cool trick that kind of takes data validation to the next level.
What if we have 2 drop-down boxes, and the second drop-down box is dependent on the first one?
So here we have a drop-down box where someone can choose a subject: Reading, Science, Math, or Geography.
And then once they choose that subject, the next drop-down box has to, kind of, dynamically have the concentrations within that subject.
Now to set this up, I'm going to go back to a back-sheet, where I've hidden all of my valid lists.
So I have a list here of the Subjects, and then for each subject I have a second list, and I've assigned that list a name.
You can say that this list is called Reading, and this list is called Science, and then there's one for Math and Geography.
Now here's how to make it work, back on the first sheet, in the second drop-down, I'm going to go to Data Validation.
And when it asks me for where my list is, I'm going to put a formula in, and my formula =INDIRECT.
INDIRECT says “Take whatever comes in the parentheses and treat it as a cell value.” And I’m going to say “The INDRECT of the answer for D2.” Click OK, and now the answer for D2 in this case is Math.
The INDIRECT says “Hey, go find the address called Math!” Which in this case is a named range.
So let me show you how it works, if we come here and change Math to be, let's say Reading, then when we come to D4, the drop-down list will get its values from the named range called Reading.
If we go back to the original drop-down and change to Geography, then this drop-down will dynamically get its list from the range called Geography.
Very cool use to create, let's call it Dependent Validation list.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
