I've talked about making the list for your data validation automatically grow with the list. In the past, I've used a macro or the OFFSET formula, but today Morne sends in a beautiful solution for either Excel 2003 or Excel 2007 to build a dynamic validation list. Episode 937 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, a note from Morne.
Morne is stationed in Afghanistan and sent in an awesome, awesome tip.
Morne’s going to get an Excel master pin for this one talking about data validation.
Data validation usually has to rely on a list that's on the same worksheet.
I've talked in the past about how you can make that list be on another worksheet if you apply a name.
I've also talked in the past about how that list can grow if you use the offset function, but Morne says, hey, there's an easier way, and he's absolutely right.
Check it out.
So, we have our list here.
I'm going to select the list and, in Excel 2007, I'm going to press CONTROL+T -- in Excel 2003, you would have done CONTROL+L -- and so it defines the data for the table.
We click OK and now I'm going to name that range.
So, I’ll call it REGIONS.
Alright.
Good enough.
Now, we come back here to SHEET 1, and we go to DATA VALIDATION, and say that we're going to ALLOW a LIST, and say the list is =REGIONS.
Click OK.
Alright.
So, there's our dropdown with the items.
Now, here's the really cool thing -- and this is Morne’s trick -- if we come back here to the data sheet and type a brand new region, the Canada region, it automatically extends the list, it automatically extends the name that refer to the list, and it will appear at the bottom of the dropdown.
Isn't that beautiful?
Morne, thanks for sending that tip in -- a great way to set up a dynamic range without having to try to figure out how to use the offset function.
Well, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Today, a note from Morne.
Morne is stationed in Afghanistan and sent in an awesome, awesome tip.
Morne’s going to get an Excel master pin for this one talking about data validation.
Data validation usually has to rely on a list that's on the same worksheet.
I've talked in the past about how you can make that list be on another worksheet if you apply a name.
I've also talked in the past about how that list can grow if you use the offset function, but Morne says, hey, there's an easier way, and he's absolutely right.
Check it out.
So, we have our list here.
I'm going to select the list and, in Excel 2007, I'm going to press CONTROL+T -- in Excel 2003, you would have done CONTROL+L -- and so it defines the data for the table.
We click OK and now I'm going to name that range.
So, I’ll call it REGIONS.
Alright.
Good enough.
Now, we come back here to SHEET 1, and we go to DATA VALIDATION, and say that we're going to ALLOW a LIST, and say the list is =REGIONS.
Click OK.
Alright.
So, there's our dropdown with the items.
Now, here's the really cool thing -- and this is Morne’s trick -- if we come back here to the data sheet and type a brand new region, the Canada region, it automatically extends the list, it automatically extends the name that refer to the list, and it will appear at the bottom of the dropdown.
Isn't that beautiful?
Morne, thanks for sending that tip in -- a great way to set up a dynamic range without having to try to figure out how to use the offset function.
Well, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
