Problem:

I have a very long list of items and want to be able to 'type ahead' and, of course, I can't!  But it is really time consuming to scroll through to select an item.

Solution:

One solution is to group the list into chunks - either by category (Food, Electrical goods and so on) or alphabetically (A_D, E_H and so on), and then use INDIRECT to use a subset in data validation.

Steps:

1 Create a list of groups and NAME IT

2 Copy the groups then Paste Special » Transpose

3 Copy (or type) the items under the appropriate group header

4 Name each group, using the label in the top

5 Insert an extra column for the Group - and use Data Validation, specify the Group name as list source

6 In the Item column, use Data validation - and type

Then, when you choose a group (in column C) it will only show members of that group in column D.