+ 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
    2010
    Posts
    6

    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
    Banned User!
    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
    2010
    Posts
    6

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,589

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Banned User!
    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
    2010
    Posts
    6

    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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    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



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

    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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    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. select one value from drop-down and return multiple corresponding values
    By depillis@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-18-2006, 02:05 PM
  7. [SOLVED] with a drop down list in excel how do I select multiple values
    By Keith H in forum Excel General
    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