Home > Excel for Business > 

Use a Combo Box with Many
Criteria in an Excel Database

July, 2006

Greg,

First, I'll restate the problem. You have a database that looks something like the figure.

Your database has hundreds of products, not the four shown here. Each row includes your part number, the supplier, the supplier's code, a general description, and specifications about the product...along with other data that doesn't enter into this discussion.

You're looking for an easy way to find the part number for a given product.

As a general rule, I hesitate to offer a VBA solution, for several reasons.

• Most Excel users in business have more than enough to do without also trying to learn how to program.
   
• When an accountant or analyst does learn VBA, he usually writes code that no one else in the department understands. This becomes a big problem when he moves on to another job.
   
• If some outsider -- like myself -- writes the program, support also becomes difficult.

Therefore, it's usually best to find a non-programming solution.

Although there are several non-programming ways to give you the results you're asking for, I'll discuss the one that's probably the most powerful.

Excel offers a type of list box called a Combo Box. This figure shows it in a spreadsheet:

When you click on the down arrow, you see the list of labels shown in column G. When you click on one of the items in the list, Excel writes the index number for the list to cell C1, and the part number is displayed in cell C2.

The items in rows 1 through 4 in the figure probably would be on another sheet in your workbook. I'm showing them in the same sheet to make this description easier to follow.

The figure several has several key elements. Not all of them are obvious. Let's take them one at a time.

Border Rows

Whenever you refer to ranges in an Excel database, there's always the risk that someone will add data above or below the range that you've referenced. Therefore, I always try to use border rows in my Excel databases, as shown in rows 6 and 10.

When you use border rows, always define your range names from border row to border row. And always insert new data between border rows. That way, your named ranges always enclose the data you expect.

The only exception to this rule is when your data area needs to have a pretty format because it's part of a good-looking report. In those rare cases, you make the best compromises you can.

Range Names

Each column of data uses the range name shown in row 5 in the figures above and below. Each of those names uses upper-lower case and no spaces.

In this case, the range names for the data are all plural...other than Seq.  This is because I planned to display singular versions of some of the columns, as shown in cell C2. So column B contains part numbers ("PartNums") and cell C2 contains a single part number ("PartNum").

To assign the database's range names, select the range A5:G11; choose Insert, Name, Create; in the Create Names dialog, ensure that only Top Row is selected; then choose OK. This command assigns the labels in row 5 as range names beginning in row 6 and ending in row 11.

To assign the other two range names, select the range B1:C2; choose Insert, Name, Create; in the Create Names dialog ensure that only Left Column is selected;  then choose OK.

Create the Labels

The labels (shown below) in column G aren't in the database you sent me. I created them to give me one column of labels that I could use to find your part number easily.

It's easy to create the labels. In cell G7 enter this formula:
=C7&", "&E7&", "&F7
...and then copy it down the column as shown.

This formula merely concatenates the labels in the row for the suppliers, descriptions, and specifications. Each label is separated by a comma followed by a space.

You can, of course, create any formula you want for your labels. If you prefer to leave the suppliers' names out of the labels, or if you want additional information from some other column, just write your label formulas as needed. Of course, this formula can refer to as many columns of data as you want.

Create the Combo Box

To create the Combo Box, follow these steps:

1. Right-click on one of your toolbars. If the Forms item in the list isn't checked, click on it to display the Forms toolbar.

2. In the Forms toolbar, click on the Combo Box icon, shown here.

3. Your pointer will change to a cross. Use it to draw a Combo Box about the size of the one shown in the figure above.

4. Right-click on the Combo Box and choose Format Control.

5. In the Control tab, type the range name "Labels" (without the quotes) in the edit box for the Input Range. And type the range name "Index" (no quotes) in the edit box for the Cell Link. Then choose OK.

6. Enter this formula into cell C2:  =INDEX(PartNums,Index)

To return a part number, click on the Combo Box, which drops down to reveal a list of 8 items. Choose the item you want from the list. After you do so, Excel writes the index number for the item to the Index cell. If calculation is set to automatic, the PartNum formula calculates to return the part number for the selected item. Otherwise, press F9 to recalc.

You probably want the labels to be in alphabetic order to make them easy to find. To do so, sort your data area on column G. Whenever you want your data in its original sequence, sort by the sequence column.

Hope this helps,

Charley Kyd
June, 2006

(Email Comments)