Rico S introduced me to ProgramKey+K to Pick from a Dropdown List. This gives you a drop-down menu without data validation. I can see that this feature could be pretty cool, but I need something easier than Program Key, K, Down, Down, Down, Enter, Enter.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2327. Pick from Dropdown list not using data validation. Hey, welcome back to the MrExcel Netcast.
I'm Bill Jelen. Today a great tip sent in by Rico S.
I'm talking about using the Program key plus K to pick from the drop down list.
And I'm not talking about a drop down list is created by data validation.
Those are a pain if you have 1000 of them it's going to take a lot of overhead and I'm not talking about AutoComplete, although I think what we're talking about is related to AutoComplete. So, we have a nice little column there.
Apple, banana, cherry just type "A".
It says Apple I press Enter and move on with my life. "B".
Enter. Cherry.
"C" for cherry move on with my life and that works great until we come up with something ambiguous, like applesauce.
Right now, well, it still works, but if I type "A" doesn't know what to do right?
I have to keep typing until I get out to something that's not ambiguous and then press enter.
So Rico says: here's what we're going to do.
We're going to use the program key if you don't have this, it's Shift+F10 program key - press and release the program key, and then press and release K.
What's that doing?
Well if I press and release the program key you see that the K is called. "Pick from dropdown list".
I don't know how long this has been there. Is this new? Is this old?
Maybe it's been there for 20 years and I've never noticed it.
But check out what it does so I come here.
Program key K and it gives me a list that I can arrow through to choose right! And what's weird tab doesn't work, but if I press enter it chooses applesauce but leaves me in that cell so it almost comes down to going to press Program key.
K, Down, Down, Down, Enter Enter to move to the next one.
Program key K down, down, down down enter enter to move to the next cell.
It's pretty cool that there's a dropdown list there that just gets created on the fly.
You don't need to set up data validation, but you know my first reaction is...
Is this somewhere in the ribbon?
Because what I want to do I want to right click and then right click, say pick from Dropdown list add to quick access toolbar.
That way I could maybe get an easier keyboard shortcut than this admittedly weird program key K. I'm just not a fan of that.
I'd love to have it be the second item in the quick access toolbar when we go to customize quick access toolbar, then All Commands, pick from Dropdown list is not there and I've looked for everything else "Select from Dropdown list".
"Choose from drop down list" and it just does not appear to be anywhere.
How can it be that this got in the product only as a right-click key and not as an actual command up in the ribbon?
Isn't that weird?
And here let's check it out, test some limits here so I have about 193 phrases.
I think it started in row 3 on this 194 phrases - lots of different phrases.
Program key K and it offers an alphabetical list of everything above.
Even all the way down to.
Yeah, so 193. You know it works with 190 items.
It's a cool feature, kind of hidden, especially hidden for me.
because I never think to right-click and I just kind of wonder where it came from.
So hey, I want to hear from you if you've used this. If you have any tips for this.
If you can figure out anyway to assign a better shortcut key to it other than a SendKeys macro, that's going to be so unreliable. But maybe it's better.
I don't know, let me know. Leave it note down in the YouTube comments.
Thanks very much.
Thanks to the people who subscribed recently I'm trying to get that 100,000 level.
If you like this video please subscribe and ring that bell.
The new book MrExcel 2020 seeing Excel Clearly. Click the "I" in the top right hand corner.
I want to thank you for stopping by.
We'll see next time for another netcast from MrExcel.
I'm Bill Jelen. Today a great tip sent in by Rico S.
I'm talking about using the Program key plus K to pick from the drop down list.
And I'm not talking about a drop down list is created by data validation.
Those are a pain if you have 1000 of them it's going to take a lot of overhead and I'm not talking about AutoComplete, although I think what we're talking about is related to AutoComplete. So, we have a nice little column there.
Apple, banana, cherry just type "A".
It says Apple I press Enter and move on with my life. "B".
Enter. Cherry.
"C" for cherry move on with my life and that works great until we come up with something ambiguous, like applesauce.
Right now, well, it still works, but if I type "A" doesn't know what to do right?
I have to keep typing until I get out to something that's not ambiguous and then press enter.
So Rico says: here's what we're going to do.
We're going to use the program key if you don't have this, it's Shift+F10 program key - press and release the program key, and then press and release K.
What's that doing?
Well if I press and release the program key you see that the K is called. "Pick from dropdown list".
I don't know how long this has been there. Is this new? Is this old?
Maybe it's been there for 20 years and I've never noticed it.
But check out what it does so I come here.
Program key K and it gives me a list that I can arrow through to choose right! And what's weird tab doesn't work, but if I press enter it chooses applesauce but leaves me in that cell so it almost comes down to going to press Program key.
K, Down, Down, Down, Enter Enter to move to the next one.
Program key K down, down, down down enter enter to move to the next cell.
It's pretty cool that there's a dropdown list there that just gets created on the fly.
You don't need to set up data validation, but you know my first reaction is...
Is this somewhere in the ribbon?
Because what I want to do I want to right click and then right click, say pick from Dropdown list add to quick access toolbar.
That way I could maybe get an easier keyboard shortcut than this admittedly weird program key K. I'm just not a fan of that.
I'd love to have it be the second item in the quick access toolbar when we go to customize quick access toolbar, then All Commands, pick from Dropdown list is not there and I've looked for everything else "Select from Dropdown list".
"Choose from drop down list" and it just does not appear to be anywhere.
How can it be that this got in the product only as a right-click key and not as an actual command up in the ribbon?
Isn't that weird?
And here let's check it out, test some limits here so I have about 193 phrases.
I think it started in row 3 on this 194 phrases - lots of different phrases.
Program key K and it offers an alphabetical list of everything above.
Even all the way down to.
Yeah, so 193. You know it works with 190 items.
It's a cool feature, kind of hidden, especially hidden for me.
because I never think to right-click and I just kind of wonder where it came from.
So hey, I want to hear from you if you've used this. If you have any tips for this.
If you can figure out anyway to assign a better shortcut key to it other than a SendKeys macro, that's going to be so unreliable. But maybe it's better.
I don't know, let me know. Leave it note down in the YouTube comments.
Thanks very much.
Thanks to the people who subscribed recently I'm trying to get that 100,000 level.
If you like this video please subscribe and ring that bell.
The new book MrExcel 2020 seeing Excel Clearly. Click the "I" in the top right hand corner.
I want to thank you for stopping by.
We'll see next time for another netcast from MrExcel.
