Using Defined Names

[snowbunny]

Hello,

I am trying to create a document for my work that I need Name Manager formula. This formula is working just fine, but I have quite a bit of names and I was wondering if there is a way to just start typing in the information that I need and have it auto-fill the rest in without using the drop down arrow.

[shg]

You mean autocomplete the name while you're typing in a formula? No.

[snowbunny]

No sorry. I guess I should rephrase. That wasn't what I meant. Under the Formulas tab in the defined names area, I can create a drop-down list. When I finished creating my list that I wanted, I went to type in where the cell is located and it only allows me to click the drop down arrow and search for the name that I want. I was hoping that instead of always clicking on the drop down arrow, I could just start typing in the name and it would predict or forecast the name that I want. I have a very long list of names, in the hundreds and it is a pain to have to always select the drop down arrow and scroll down to locate it. Not very efficient.

[shg]

You can just type the whole name in, right?

I reckon you could write a macro that lists all the names in a listbox or combobox, and code it to do autocomplete on that, but it seems like a lot of effort for not much gain.

I expect it doesn't auto-complete because you can also use the Names box to add a named range to the worksheet or workbook.

[gebobs]

Unless I am not understanding, Excel should autocomplete names used in your equations via a context list based on your defined names and Excel reserved names.

For instance, if I have a defined name "AverageBin" and start typing in a formula beginning with "=2.5*av" a context list will appear with a list of functions and names that begin with "av" and there is AverageBin right between AVERAGEA() and AVERAGEIF(). You can navigate the list with arrow keys and choose one by pressing tab.

It's really useful if you have defined tables. Say I have a table called Inventory with fields Part, Received, Qty. If I start typing a formula "=inv", the context list pops up and there's my table. I press tab to select it and then type in a left bracket i.e. "=Inventory[". Another context list now pops up with all my fields (Part, Received, Qty).