This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Combine Validation with AutoComplete
Problem: The Validation dropdown is horrible for keyboard people. You can’t quickly jump to an item in the dropdown.
Strategy: Combine AutoComplete with Validation. Several viewers of the Learn Excel from MrExcel podcast sent in this idea when I complained about the lack of keyboard support for validation. Thus, I am guessing that the trick must be fairly widespread.
Say that you want to enter products in column D.
Insert several rows above the D1 heading and store the list above the heading. Set up the validation below the heading.
When someone who is a mouse person comes along, they will open the dropdown and use the mouse as usual.
Figure 1357 Validation works with the mouse.
If someone who is a keyboard person comes along, they can start typing the entry. The AutoComplete will offer an item from the list above the heading.