Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop Down List Question (Excel 2003)

    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. [img]/forums/images/smilies/smile.gif[/img] Anything to make their life easier is always welcome.

    Thank you in advance for your assistance.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down List Question (Excel 2003)

    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 [img]/forums/images/smilies/cool.gif[/img], 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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List Question (Excel 2003)

    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.

  4. #4
    New Lounger
    Join Date
    Jan 2005
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down List Question (Excel 2003)

    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?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List Question (Excel 2003)

    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:
    <UL><LI>ColumnCount: 2
    <LI>ColumnWidths: for example 0;144 (the default unit is points, 1 inch = 72 points)
    <LI>LinkedCell: the cell address of the cell that displays the selected obnoxious number
    <LI>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.

  6. #6
    New Lounger
    Join Date
    Jan 2005
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down List Question (Excel 2003)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List Question (Excel 2003)

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

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down List Question (Excel 2003)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List Question (Excel 2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •