Drop Down List Question (Excel 2003)

Topic

New Reply

I want to do a drop down list where the list shows one value, but when the selection is clicked, another value is shown, and I’d like to reference an outside array. In the array, Column A is the obnoxious part number (for example: 1234abcd, 2345bcde, etc), and Column B is the description of the part number (for example: Widget A, Widget B, etc). I would like the drop-down list to reflect the values on Column B, but when selected, actually show the obnoxious part number in Column A. So, for example, they’d use the Drop Down list to select Widget A, but once that was clicked, the value reflected in the cell would be 1234abcd. That way the engineers who are filling out the form will not have to reference a separate sheet to figure out which obnoxious part number is the one that needs to be selected.

Is this possible at all? If not, Plan B is to have them go to the Excel workbook with both the number the descriptions, and copy and paste the data. They don’t seem to like the drop-down list I’ve currently set up with only the numbers (via “Data-Validation-Settings), and they are currently having to flip back and forth from page to page to figure out which number to use. But they’re engineers – this is a bit complicated for them. Anything to make their life easier is always welcome.

Thank you in advance for your assistance.

Reply | Quote

Replies

Hi,

I’ve attached an example sheet that uses the combobox control from the control toolbox toolbar.

It is set up to show two columns (A and , but the width of the first column in the control is set to 0 so it doesn’t show.
The textcolumn is set to the second column so the descriptions show, but the boundcolumn is set to the first column, so you get the code of column 1 as a result in the linked cell.

Reply | Quote

This will definitely work for me. But I need more specific instructions on how to create this because I have very little experience with combo boxes. I know how to create the box (click on the button, drag cursor where the box is to go, click on it to move and resize), but once the box is created, how do I link it to the array so that it will show only the Friendly Description column, and that the cell will reflect the obnoxious box?

Reply | Quote

While still in design mode, and with the combo box selected, click the Properties button on the Control Toolbox (it’s the second button in the default layout, next to the Design Mode button).
The properties you want to set are:

• ColumnCount: 2
• ColumnWidths: for example 0;144 (the default unit is points, 1 inch = 72 points)
• LinkedCell: the cell address of the cell that displays the selected obnoxious number
• ListFillRange: the address of the parts table
  [/list]There are some other properties that can be useful, but these four are the essential ones for your problem.
  When you’re done, switch off Design Mode.

Reply | Quote

I think I see why I’ve been having problems with this. I click on the “Control Properties” button, but I don’t see those properties that you list in any of the tabs that come up. the Control tab only has Input Range, Cell Link and Drop Down Lines as an option. The “Prpperties” tag only gives me options for Object Positioning and a check box for Print Object.

To creat the box, I am using the Combo Box option in the Forms box. Should I be creating the box from another toolbar or from somewhere in the menu lists?

I really appreciate you all helping me with this problem. I’m usually pretty slick about these things, but I don’t seem to be on the same page as you guys.

Reply | Quote

You should use the Control Toolbox toolbar to create the combo box, not the Forms toolbar.

Reply | Quote

The one time that using the Forms toolbar for this is when you exchange files with the Mac versions of Excel. MS did not include ActiveX support on the Mac versions.

Reply | Quote

Even in a Windows-only setup, the controls from the Forms toolbar can be very useful, but in this specific situation, the combo box from the Control Toolbox is easier to use because it can have multiple columns.

Reply | Quote

My example is basically the same as Jan Karel’s. I have attached it because it demonstrates how to use a dynamic named range as list fill range for the combo box. If you add items to the list in the Parts sheet, they will automatically be displayed in the combo box on the first sheet.

Reply | Quote