Excel has supported the List option in Data Validation for a decade. But let's face it: it is not easy to select from the list. You can't start typing and have the list shorten to just the matches, also known as Look Ahead.
Today, a cool trick from Bob Umlas and his friend Alfred. By creating a few hundred range names, you can call up the list quickly. Type the first few letters of the name and you will jump to the names that match those letters.
Thanks to Create Names from Selection, this is actually easy to set up.
Today, a cool trick from Bob Umlas and his friend Alfred. By creating a few hundred range names, you can call up the list quickly. Type the first few letters of the name and you will jump to the names that match those letters.
Thanks to Create Names from Selection, this is actually easy to set up.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2380. Look Ahead When Selecting Names.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
This is an eternal problem that we've always had. You can set up Data Validation.
But, there's no good way to get through this list quickly. Like, you can't use the wheel mouse.
You can't type the first few letters of what you're looking for. You are just stuck unless you scroll, scroll, scroll, scroll, scroll, scroll, scroll.
And a cool trick here from Bob Umlas. That he got from his friend, Alfred.
Let me show you how it's working. First it's actually very satisfying.
How it works: Alfred came up with this.
To select a name, the first thing we are going to do is type an equals and then a backslash. And then it gives us this whole big list.
And the beautiful thing is if we know that we want someone who starts with J.
I start to type the J and the list shortens to everyone who starts with a J. And then I type an "a". And then I get just the J-A's.
JAV. Alright there's Javier.
And now that I have that name I can just press Tab, Enter. And I am done. Alright, let's try someone in the C's. So, Equal, Backslash, C and then E.
Alright. Tab Enter. Equal Backslash Y. Oh yeah, there is a couple of those.
I can use the up or down arrow keys to choose. Right it actually works pretty well. It's a cool trick.
So, let's go back to my original workbook and I will show you how to set this up. Our names are over here in column A.
I am going to insert a new column to the left of that. We are going to use concatenation.
So equal sign, quote, backslash, end quote, ampersand, the name over in the Column b, like that.
And we're going to do that so we can get a list of what eventually will become range name.
So i choose those names and their values and then here on the Formulas tab, I am going to use Create Name From Selection.
It always chooses top row we're going to uncheck that and say that everything in the left column is going to name the cell to the right of it.
So this cell B5 is going to be called backslash Alberto Cromwell. And it'll just happen automatically. So, click OK. Right. And Bam!
We just created a couple hundred names.
And now the beautiful thing is we don't have to use Data Validation in any cell. We can just come here. Equal sign, Backslash. Type a K. E. Keanu. Right?
It actually is much faster I think than Data Validation.
Now one hassle here is we created in this case 200 names. Is that a problem?
I don't think it's any worse than having 200 data validation boxes in a whole range. And it's great. It will just work anywhere you want it to work.
So, pretty cool trick.
Thanks to Bob Umlas for sending that along and to his friend Alfred for figuring that out.
Hey, if you like these tips please, down below click Like, Subscribe, and Ring the Bell. Feel free to post any questions or comments down in the comments below.
Check out Bob Umlas's book, Excel Outside The Box. There's also More Excel Outside the Box.
Click that i in the top right hand corner. It will take you to a link where you can read more about the book.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
This is an eternal problem that we've always had. You can set up Data Validation.
But, there's no good way to get through this list quickly. Like, you can't use the wheel mouse.
You can't type the first few letters of what you're looking for. You are just stuck unless you scroll, scroll, scroll, scroll, scroll, scroll, scroll.
And a cool trick here from Bob Umlas. That he got from his friend, Alfred.
Let me show you how it's working. First it's actually very satisfying.
How it works: Alfred came up with this.
To select a name, the first thing we are going to do is type an equals and then a backslash. And then it gives us this whole big list.
And the beautiful thing is if we know that we want someone who starts with J.
I start to type the J and the list shortens to everyone who starts with a J. And then I type an "a". And then I get just the J-A's.
JAV. Alright there's Javier.
And now that I have that name I can just press Tab, Enter. And I am done. Alright, let's try someone in the C's. So, Equal, Backslash, C and then E.
Alright. Tab Enter. Equal Backslash Y. Oh yeah, there is a couple of those.
I can use the up or down arrow keys to choose. Right it actually works pretty well. It's a cool trick.
So, let's go back to my original workbook and I will show you how to set this up. Our names are over here in column A.
I am going to insert a new column to the left of that. We are going to use concatenation.
So equal sign, quote, backslash, end quote, ampersand, the name over in the Column b, like that.
And we're going to do that so we can get a list of what eventually will become range name.
So i choose those names and their values and then here on the Formulas tab, I am going to use Create Name From Selection.
It always chooses top row we're going to uncheck that and say that everything in the left column is going to name the cell to the right of it.
So this cell B5 is going to be called backslash Alberto Cromwell. And it'll just happen automatically. So, click OK. Right. And Bam!
We just created a couple hundred names.
And now the beautiful thing is we don't have to use Data Validation in any cell. We can just come here. Equal sign, Backslash. Type a K. E. Keanu. Right?
It actually is much faster I think than Data Validation.
Now one hassle here is we created in this case 200 names. Is that a problem?
I don't think it's any worse than having 200 data validation boxes in a whole range. And it's great. It will just work anywhere you want it to work.
So, pretty cool trick.
Thanks to Bob Umlas for sending that along and to his friend Alfred for figuring that out.
Hey, if you like these tips please, down below click Like, Subscribe, and Ring the Bell. Feel free to post any questions or comments down in the comments below.
Check out Bob Umlas's book, Excel Outside The Box. There's also More Excel Outside the Box.
Click that i in the top right hand corner. It will take you to a link where you can read more about the book.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
