+ Reply to Thread
Results 1 to 9 of 9

Select multiple values with drop down menu

  1. #1
    Registered User
    Join Date
    03-23-2018
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Office 365
    Posts
    8

    Select multiple values with drop down menu

    Hello excel community,

    I would like to request assistance on a sheet I am making. I hope that it's possible to create the thing I need, and that there is anyone here that can help me out.
    I think it would be the best to show you with an image, but I am not allowed to post images or links yet. I have tried to show te situation I'd like to create below:

    Values wanted to show
    25
    60
    50

    Drop down menu choice: A

    A= 25/60/50
    B= 40/50/80
    C= 50/65/90


    I have one table with multiple values. Each of the choices represents a series of values. I'd like to have the values automatically change to the values below A, when this option is selected in a drop down menu. I don't know if this is possible in excel, but I'd be glad to get some help if it is!

    Thanks in advance,

    Mark
    Last edited by markwheelie; 08-07-2018 at 10:31 AM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Select multiple values with drop down menu

    You can do it with data validation.

    Select the 'list' option from the data validation editor, an point it to your table with a formula similar to this one.

    =INDEX(Sheet2!$A$2:$C$4,0,MATCH(A1,Sheet2!$A$1:$C$1,0))

    Where Sheet2!$A$2:$C$4 refers to the list of values to return in the dropdown (options for A in column A, B in column B, etc.) A1 refers to the cell with the first dropdown / option selection.
    Sheet2!$A$1:$C$1 refers to the column headers above the table of results, that the first selection can be matched to.

  3. #3
    Registered User
    Join Date
    03-23-2018
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Select multiple values with drop down menu

    Hi Jason,

    Thanks for your explanation. Even though I think that using your method would work perfectly, I couldn't get it to work. I assume I made a few mistakes in the basics of setting this up. Any chance for you to PM me with a small example sheet? I could give an example of the use case to make it more clear:

    I have 3 types of wooden sheets. They all have 3 values: Length/Width/Thickness
    All data has been stored in a simple table that has type1/type2/type3 in B1-D1. Below that are the values for Length/Width/Thickness.


    I'd like to select one of 3 sheets in the drop down menu (sheet1/sheet2/sheet3), and then have the corresponding values as in the table show up in cells A1,A2 and A3.

    Hope you can help me once more.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: Select multiple values with drop down menu

    Nothing should go via PM - please read the forum rules regarding keeping everything here.

    What you should do, please, is attach your attempt at getting it done.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Select multiple values with drop down menu

    See if this helps. I've set up the validation and coloured the cells in both sheets to that you can see which cells link to which part of the table.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-23-2018
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Select multiple values with drop down menu

    Thanks for the example Jason. It's more or less what I wanted, but there is just one thing that differs from what I needed. I have edited your example to show what exactly I'd like to do. Thanks for the help.
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Select multiple values with drop down menu

    Like this???

    =INDEX(Sheet2!$B$2:$D$4,MATCH(Sheet1!B1,Sheet2!$A$2:$A$4,0),MATCH(Sheet1!$A$1,Sheet2!$B$1:$D$1,0))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Registered User
    Join Date
    03-23-2018
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Select multiple values with drop down menu

    Thank you so much! That is exactly what I was looking for. I'll play around with the example so I can build it by myself next time.

    Again, thank you to Glenn and Jason for helping me out.
    Last edited by AliGW; 08-08-2018 at 03:35 AM. Reason: Unnecessary quotation removed.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Select multiple values with drop down menu

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to select multiple values from a drop down list
    By Kingofnowhere in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-02-2017, 02:48 AM
  2. HTML Select Multiple Values in Option Menu
    By San_K in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2015, 03:38 PM
  3. Replies: 7
    Last Post: 06-22-2014, 03:33 AM
  4. [SOLVED] Drop Down Menu changes changes multiple cell values
    By dompoma in forum Excel General
    Replies: 5
    Last Post: 05-10-2013, 12:21 AM
  5. select multiple items from drop-down menu
    By jcavigli in forum Excel General
    Replies: 2
    Last Post: 05-25-2008, 11:50 AM
  6. Replies: 0
    Last Post: 08-18-2006, 02:05 PM
  7. Replies: 1
    Last Post: 07-13-2006, 01:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1