Excel: a dynamic look ahead to select items from a List

I discovered a neat trick that provides a dynamic look ahead to select items from a List

The other day, I spent some time searching via Google to find a simple way to quickly choose items from a list.

I saw several suggested techniques that would provide a look ahead capability for selecting items from an enhanced Drop-Down Box.

I was not satisfied with what I found.

Later, while I was working on my project, I noticed that as I entered a formula into Excel, Excel would then offer a list of suggestions.

That included both functions and Ranges.

I then realized that I could take advantage of this auto-suggestion feature to get the look ahead functionality what I was looking for.

Using a special symbol, I generated a Range Name for each person in the List.

You can use different symbols to manage different lists.

For example, using a collection of Random Names:

The list is referenced in alphabetical order.

Bill Jelen posted a YouTube showing how this technique can be used.

I have explored several special characters that can be used as the first Character for a Range Name.

This collection of characters will enable the development of various lists in a workbook.

A special symbol is entered by using the Number Pad.

Hold Down the Alt Key and then type 0XXX.

As an aside, I use the following code snippet to selectively delete collections of Range Names.

Here are links to the example Workbooks:

Contact me for more information:


Author: excel1star

Senior Excel VBA Automation Developer. Ex Aerospace Engineer with Grumman Aerospace

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: