Youtor
Autocomplete Entries With Excel Drop Down List
>> YOUR LINK HERE: ___ http://youtube.com/watch?v=vOOuSe_s6pQ
To make data entry easier, create a drop down list in an Excel cell, using data validation. • This saves you time, and prevents errors, but has some limitations. • -The font size is small, and can't be changed. • -You can only see 8 items at a time, and have to scroll to see the other items. • -There's no autocomplete - pick from the list, or type the full entry. • This video shows you a workaround for those limitations. --Put one hidden combo box on the worksheet • --Double-click a data validation cell, and the combo box appears • In the combo box: • -you can adjust the font size, and the number of rows. -entries autocomplete as you type • Go to my website, and get the completed workbook from this video • http://www.contextures.com/xlDataVal1... • For the example that works when you single-click on a cell, go to this page: • http://www.contextures.com/xlDataVal1... • Instructor: Debra Dalgleish, Contextures Inc. • Get Debra's weekly Excel tips: http://www.contextures.com/signup01 • More Excel Tips and Tutorials: http://www.contextures.com/tiptech.html • Subscribe to Contextures YouTube: https://www.youtube.com/user/contextu... • #ContexturesExcelTips • '----- • VIDEO TRANSCRIPT - Abridged • To make it easy for people to enter data in Excel, you can create drop down lists of items. • In this cell, we have a list of weekdays. I click on that Arrow, click a weekday, and it fills in the cell. • The same thing for months. Here's a list. It only shows 8 items at a time, but I can scroll down and click December. • Those lists are on another worksheet, and then use data validation, on the Data tab, to set that up. • There are a few limitations though. The font is very small and you can't change it. • And you can only see eight items at a time. • This doesn't auto complete. Data validation doesn't help you fill in the word. • Excel might help you, if it can copy something that you've already entered above, but the data validation itself won't help me type January. • If I start typing, I have to fill in the whole word and move on to the next cell. • So data validation is helpful, but I've come up with a work around that you can download on my website and it uses a combo box over cells that have a data validation drop down. • In this corner, hidden away, is a little combo box, and it pops up over a cell, when you double click on it. • So if I double click here, the Combo box comes out of hiding, goes over this cell, and picks up the same data validation list that the cell uses. • So when I click the arrow, I'll see the list of weekdays. I can click on one, press Enter to go to the next cell. • I'll double click again, and for this one I could start typing T, and it fills in Tuesday, but if I wanted Thursday, I'll type in h, and then press Enter. • So it helps with the typing, and if we look at the months, I've changed the number of rows that show, so we can see 12. • You can adjust that setting, as well as the font size, and select. And again here, we can type. • So if I want to type August, just have to type two letters and it's filled in for me. • To see the Combo box, I'm going to double click on a cell and then go to the Developer tab on the ribbon, and there's a Design Mode button. • When I click that, I'm able to make changes to that Combo box. I can click on the Properties button and here are all the things you could change about that Combo box. • So you get a lot more control over it than you have over data validation. • So you could come down here, and go to the font. Instead of Arial font, you could change the font, and the font size, to whatever suits you. • And here's where I changed the list rows. This is 12 you could make that a larger number. You'd want to pick a number that's going to fit on the screen. You don't want anything that's too huge, but you can adjust that setting. • And then when you're done making changes, turn off Design Mode, and you're ready to go again. • So if you download the sample file from my website, you'll be able to copy it all into your workbook. • Copy the Combo box and the code onto the worksheet. So if I right click View Code, you'll see that there's code that runs, when you double click the cell. • You don't have to understand all this code, you would just have to get it into your workbook. • For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website, at www.contextures.com
#############################
