Store your validation lists on a separate worksheet and have the dropdown boxes automatically expand when you add new items to the list. Episode 584 shows you how. If you'd like to try this out, download the sample workbook. Monday in the USA is our Labor Day holiday and I'll be back Tuesday, September 4th.
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.
For the past couple of days I've talked about Data Validation.
That's a question that Brent sent in.
And then I just want to touch base, on a cool spreadsheet, that I set up for my wife Mary Ellen.
Mary Ellen needed a simple place, where she could have drop-down list.
And she wanted to be able to maintain those lists.
And Mary Ellen just is not the most proficient person in excel.
I didn't want to try to explain how to set up data validation.
So, we go to Format Sheet Unhide.
Let me unhide the sheet from yesterday.
And you'll remember that we learned yesterday that you can set up Data Validation List on another worksheet, provided that you provided named range.
Well, for Mary Ellen I setup Insert>Name >Define.
I set up a dynamic range that said that for example, the ServList is not hard coded to A2 to A6.
Instead it uses the OFFSET Function.
The offset function is this amazing function that says, hey start in A2.
go down to 0 rows, 0 Columns.
That's fine.
But then how many rows do I want to include, will use the count of how many items are in column A minus 1.
So, I have a heading in column A.
And basically, she can type as many items as she want, in a contiguous range.
Going down column A and if there's eight today, this named range will include all 8 items.
Very very cool!
And then what happens is when we go back to our original list.
She has the drop-down.
So there's item is 1 to 5.
If tomorrow her manager says, hey I need you to add a new item.
Will come here and say service 6 and now from then on the drop-down, will offer service six.
And a really amazing feature, the OFFSET Function.
What we'll do is I'll take a workbook,similar to this and I'll upload it to the blog the MrExcel podcasts blog, next to today's entry.
So, that way you can actually download this and take a look.
Hey Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
For the past couple of days I've talked about Data Validation.
That's a question that Brent sent in.
And then I just want to touch base, on a cool spreadsheet, that I set up for my wife Mary Ellen.
Mary Ellen needed a simple place, where she could have drop-down list.
And she wanted to be able to maintain those lists.
And Mary Ellen just is not the most proficient person in excel.
I didn't want to try to explain how to set up data validation.
So, we go to Format Sheet Unhide.
Let me unhide the sheet from yesterday.
And you'll remember that we learned yesterday that you can set up Data Validation List on another worksheet, provided that you provided named range.
Well, for Mary Ellen I setup Insert>Name >Define.
I set up a dynamic range that said that for example, the ServList is not hard coded to A2 to A6.
Instead it uses the OFFSET Function.
The offset function is this amazing function that says, hey start in A2.
go down to 0 rows, 0 Columns.
That's fine.
But then how many rows do I want to include, will use the count of how many items are in column A minus 1.
So, I have a heading in column A.
And basically, she can type as many items as she want, in a contiguous range.
Going down column A and if there's eight today, this named range will include all 8 items.
Very very cool!
And then what happens is when we go back to our original list.
She has the drop-down.
So there's item is 1 to 5.
If tomorrow her manager says, hey I need you to add a new item.
Will come here and say service 6 and now from then on the drop-down, will offer service six.
And a really amazing feature, the OFFSET Function.
What we'll do is I'll take a workbook,similar to this and I'll upload it to the blog the MrExcel podcasts blog, next to today's entry.
So, that way you can actually download this and take a look.
Hey Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
