Jonathan is frustrated that his validation scrollbars start in the midst of several blank cells that he set up in his validation list. He was doing this to allow room for future growth in the list.
Instead, Episode 720 will show you how to set up a dynamic named list using the OFFSET function.
This blog is the video 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!
Instead, Episode 720 will show you how to set up a dynamic named list using the OFFSET function.
This blog is the video 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. Question today from Jonathan talking about data validation.
We're talking about validation over the last couple of weeks.
He said you know I have a situation where I have a list now, but the list may grow.
Right now there's 16 items in the list but one allow room for more items to be typed at the bottom and he was frustrated that when he set up validation saying that the list could go for maybe A1 to A100 that it was screwing up the scroll bar.
Basically if he said if the validation was with way too many cells and then open that the scrollbar tended to show up down here at the bottom with nothing and then he had to scroll up all the time.
That just wasn't a good solution.
Well. There's a somewhat complicated way around this and we're going to set up a named range.
We're going to "Insert" "Name" "Define" and instead of just specifically typing a range we're going to create a formula that points to a range.
We're going to use the OFFSET function.
The offset function takes 5 arguments it says we want to start from one specific cell.
So in this case it's the Lists worksheet, cell A1.
I want to go down 0 rows and right 0 columns. Those are two arguments.
Basially we're not going to use here, but I have to put zeros in and then it wants to know how tall of a range we want to refer to.
So I'm going to use the COUNTA function and basically count how many entries are in column A of the Lists worksheet.
Now ofcourse you can't have any blanks here or this won't work.
And then finally how many columns wide is it going to be.
That would be 1 column wide so I type all that in.
Click add and I now have a name called a AList so I can set up validation out very easily.
Data validation.
Allow a list and the source =AList Click OK, and you'll see that the drop-down now lists Just the items in my list and it doesn't go too far.
The great thing is if I would come out of this list and type a new item Z9999 and come back to the drop-down, it will automatically grow to get new items at the bottom of the list.
So the whole trick there is that great little OFFSET function insert name define.
That will allow the list to automatically grow.
The other side benefit of setting up a name range is that you can now store all of your lists on a back worksheet.
So for example have all your lists on the Lists worksheet and you can actually set up validation now on another worksheet, which generally is illegal.
So we'll allow a list, have it =AList Click OK and that works fine.
You can even ofcourse hide that back worksheet.
format sheet hide.
To prevent anyone from even seeing that it's there, so thanks to Jonathan for sending in that question Thanks to you for stopping by. Will see you next time for another netcast from MrExcel.
We're talking about validation over the last couple of weeks.
He said you know I have a situation where I have a list now, but the list may grow.
Right now there's 16 items in the list but one allow room for more items to be typed at the bottom and he was frustrated that when he set up validation saying that the list could go for maybe A1 to A100 that it was screwing up the scroll bar.
Basically if he said if the validation was with way too many cells and then open that the scrollbar tended to show up down here at the bottom with nothing and then he had to scroll up all the time.
That just wasn't a good solution.
Well. There's a somewhat complicated way around this and we're going to set up a named range.
We're going to "Insert" "Name" "Define" and instead of just specifically typing a range we're going to create a formula that points to a range.
We're going to use the OFFSET function.
The offset function takes 5 arguments it says we want to start from one specific cell.
So in this case it's the Lists worksheet, cell A1.
I want to go down 0 rows and right 0 columns. Those are two arguments.
Basially we're not going to use here, but I have to put zeros in and then it wants to know how tall of a range we want to refer to.
So I'm going to use the COUNTA function and basically count how many entries are in column A of the Lists worksheet.
Now ofcourse you can't have any blanks here or this won't work.
And then finally how many columns wide is it going to be.
That would be 1 column wide so I type all that in.
Click add and I now have a name called a AList so I can set up validation out very easily.
Data validation.
Allow a list and the source =AList Click OK, and you'll see that the drop-down now lists Just the items in my list and it doesn't go too far.
The great thing is if I would come out of this list and type a new item Z9999 and come back to the drop-down, it will automatically grow to get new items at the bottom of the list.
So the whole trick there is that great little OFFSET function insert name define.
That will allow the list to automatically grow.
The other side benefit of setting up a name range is that you can now store all of your lists on a back worksheet.
So for example have all your lists on the Lists worksheet and you can actually set up validation now on another worksheet, which generally is illegal.
So we'll allow a list, have it =AList Click OK and that works fine.
You can even ofcourse hide that back worksheet.
format sheet hide.
To prevent anyone from even seeing that it's there, so thanks to Jonathan for sending in that question Thanks to you for stopping by. Will see you next time for another netcast from MrExcel.
