MrExcel's Learn Excel #937 - Dynamic Validation

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 29, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top