Laura, Erik, and Howard all sent in similar workarounds to podcast 713. Today in Episode 718, we will take a look at how to offer a validation dropdown that will allow keyboard afficianados to enter values quickly.
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!
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.
Well, last week episode 713, I talked about how frustrating is the data validation does not allow you to quickly jump to a certain item.
So, if I type B, It doesn't jump down to the B or anything like that and in 714, I showed how to do a user form to solve that, but I loved this community I had 3 great emails come in since 713 with basically similar idea.
So, clearly there's a great work around for this out there, one that I had never thought of and I want to thank everyone for sending this in.
The first one to send it in was Laura and then similar idea from Howard.
They said hey basically you know you're gonna have a drop down here in you know this cell.
What you do is just go immediately above that cell and copy your list.
So, have the list up here and you still have the data validation that way if a person is a mouse person, they can open the drop-down and choose from the list, but if there's someone who likes to type, basically what will happen is you start to type and as soon as it gets unique.
So, for example, chi for Chips or be for Berries the autocomplete will take over and it'll work.
So, that way whether there are mouse people or keyboard people it will work.
Now, the one thing is they added is if you don't want this list hanging out up here, just go to Format, Row, Hide and no one will even realize that there's anything going on and they can choose from the list very easily.
Also received an email from Eric.
Eric's email was similar, but he had a couple of good ideas.
He said when we set up the Data Validation. Let's turn off in-cell drop-down and then go to the Input Message tab and explain what the valid values are and then in that case now, we hide the the list.
So, Format, Row, Hide.
Now, we start to type first of all the input tooltip right there says exactly what the valid items are and as soon as it gets unique it will finish, and we simply hit Enter.
So, great idea kind of clever hiding your list immediately above without any blanks in between the area where you're going to be typing data and the autocomplete will take over.
Oh hey, thanks for stopping by.
We'll see you tomorrow for another netcast from MrExcel.
I'm Bill Jelen.
Well, last week episode 713, I talked about how frustrating is the data validation does not allow you to quickly jump to a certain item.
So, if I type B, It doesn't jump down to the B or anything like that and in 714, I showed how to do a user form to solve that, but I loved this community I had 3 great emails come in since 713 with basically similar idea.
So, clearly there's a great work around for this out there, one that I had never thought of and I want to thank everyone for sending this in.
The first one to send it in was Laura and then similar idea from Howard.
They said hey basically you know you're gonna have a drop down here in you know this cell.
What you do is just go immediately above that cell and copy your list.
So, have the list up here and you still have the data validation that way if a person is a mouse person, they can open the drop-down and choose from the list, but if there's someone who likes to type, basically what will happen is you start to type and as soon as it gets unique.
So, for example, chi for Chips or be for Berries the autocomplete will take over and it'll work.
So, that way whether there are mouse people or keyboard people it will work.
Now, the one thing is they added is if you don't want this list hanging out up here, just go to Format, Row, Hide and no one will even realize that there's anything going on and they can choose from the list very easily.
Also received an email from Eric.
Eric's email was similar, but he had a couple of good ideas.
He said when we set up the Data Validation. Let's turn off in-cell drop-down and then go to the Input Message tab and explain what the valid values are and then in that case now, we hide the the list.
So, Format, Row, Hide.
Now, we start to type first of all the input tooltip right there says exactly what the valid items are and as soon as it gets unique it will finish, and we simply hit Enter.
So, great idea kind of clever hiding your list immediately above without any blanks in between the area where you're going to be typing data and the autocomplete will take over.
Oh hey, thanks for stopping by.
We'll see you tomorrow for another netcast from MrExcel.
