Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KPro)

    I have an excel spreadsheet with several drop-down boxes on worksheets in various places. I want to link a cell on another sheet to the Value displayed in a Drop Down box on another sheet - NOT the Cell Link but the actual selected contents of the drop-down box. I'd like to avoid doing some kind of VBA Select Case as some of these drop-down boxes have a lot of entries (for example, every county in Texas). Can't seem to find the right magic - even to reference a Drop-Down box. I know you can do this on a Form using the index of the objects but I'd rather not do forms at this point as I want things to be very easy to deal with and a pop-up form is a complication.

    what i'm looking for is something like <font color=448800>='Worksheet'!'Drop Down 2'!Value</font color=448800> plugged directly into a cell.

    can this be done? thanks in advance...

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

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    Create a two-column table somewhere (it may be in hidden columns or on a hidden worksheet if you like). Put the index values of one of the drop down lists in the first column, and the list entries in the second column. Now, you can use a formula with VLOOKUP to get the list entry from the linked cell:

    =VLOOKUP(linked_cell,lookup_table,2,FALSE)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    it's a combo box from the Forms toolbar set directly on a worksheet. i tried data validation using List and tried to reference the list values used for the drop down box on another page but it doesn't do anything - it seems to want to look for a list of values on the page holding the data validation cell, which is not optimal for what i am trying to do.

    thanks for the quick response! i'll keep working on it.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    Not sure what you mean by drop-down box. Is it a ComboBox from the Forms toolbar or an Active-X ComboBox from the VBA Control Toolbox? It would be easier to use Data Validation. Look at the attached workbook. No VBA code, no controls. After clicking on the yellow cell, use the Data | Validation menu to see how to set it up. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    Yes, the list of values must be on the same sheet, which is a pain, but if you use a white font as I did (edited the post above and did the attachment) it works fine.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    See attached to do it your way. You just right-click to select the combo, choose format control, and set-up the linked cells. It returns an index, which you can hide. I used the INDEX function to get the name of the county. I still like Data Validation better, but it is nice that you can make the drop-down list longer with your way. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    Thanks! works fine. I put the referenced cell for the INDEX function on sheet 2 and it worked just fine.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    I was having problems getting VLOOKUP to work but another lounger suggested INDEX and that seems to do the trick.

    thanks for helping!

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    Sam

    you can use Data|Validation (List) using data of another sheet, but in the source you must declare a name defined and not the range of cells.


    Source:
    =TexasCounties
    Attached Files Attached Files

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apply a Drop Down box Value to a Cell (Win 2KPro, Office2KPro, Xcel2KP

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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