+ Reply to Thread
Results 1 to 8 of 8

How a drop down menu's list can change depending on another dropdown menu.

  1. #1
    Registered User
    Join Date
    04-22-2012
    Location
    Oldham, England
    MS-Off Ver
    Excel 2019 (Office 365)
    Posts
    6

    How a drop down menu's list can change depending on another dropdown menu.

    Hi everyone thanks for taking the time to ready my problem.

    I have a list I wish to use in a cell (A1) as a drop down list, I know how to do this (Data-Validation-list....and so on).
    The issue I have is the list is almost 200 cells long and growing so it's a lot of info to pick from, I can split the list up into 5 different ones under 5 headings, for arguments sake lets call them Blue, Red, Yellow, Green + White. Is it possible to have a dropdown list in Cell A1 were I can pick from Blue, Red, Yellow, Green, White then in Cell A2 the drop down will automatically filter or select the correct info under the chosen heading?

    Thanks in advance.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How a drop down menu's list can change depending on another dropdown menu.

    Yes, you can use the INDIRECT() method to reference the text value in A1 and use that as the Data Validation source for the drop down in A2.

    1) On sheet2, setup your lists.
    2) On Sheet2, create named ranges for each of your subsequent lists. For instance, if the items that should appear in the Blue list are in cells B1:B20:

    a) Highlight B1:B20
    b) type Blue in the Name box (the box to the left of the formula bar that normally displays the address of the currently selected cell)

    This has just "named" those cells as a group as "Blue" and can be referred to as such in formulas.

    c) repeat steps a-b with your other color ranges naming them the same way

    3) On sheet1, setup your normal DV drop down in A1.

    4) On sheet1 in cell A2, use this for your DV List formula: =INDIRECT(A1)


    NOTE: Your category options in A1 should be one-word options.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: How a drop down menu's list can change depending on another dropdown menu.

    Check this and see if it works for you:
    Dynamic Dependent Ranges.xlsx

    I have made all of the ranges dynamic since you said the list was growing. You can add more items to each group and more groups. I needed help from others on the forum here to do it.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How a drop down menu's list can change depending on another dropdown menu.

    Background on that file and the solution can be found here.

  5. #5
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: How a drop down menu's list can change depending on another dropdown menu.

    Yes, and if you (OP) want to add reputation to say thanks, JBeaucaire deserves it, not me.

  6. #6
    Forum Contributor pareshj's Avatar
    Join Date
    05-20-2014
    Location
    MUMBAI
    MS-Off Ver
    2007 & 2010
    Posts
    447

    Re: How a drop down menu's list can change depending on another dropdown menu.

    Use INDIRECT Function and in order to create dependent dropdown list, keep the headers of the columns same as the list in the first column.

    Check out the below link to create dependent drop down list in excel and follow the steps,

    http://snpinfotech.wordpress.com/201...ists-in-excel/

  7. #7
    Registered User
    Join Date
    04-22-2012
    Location
    Oldham, England
    MS-Off Ver
    Excel 2019 (Office 365)
    Posts
    6

    Re: How a drop down menu's list can change depending on another dropdown menu.

    This is perfect, thank you.

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How a drop down menu's list can change depending on another dropdown menu.

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.

+ 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. Replies: 1
    Last Post: 03-27-2013, 07:04 AM
  2. Replies: 2
    Last Post: 08-31-2012, 04:00 AM
  3. [SOLVED] Excel 2007 : new drop down menu based on dropdown menu in previous cell
    By martinpols in forum Excel General
    Replies: 3
    Last Post: 04-30-2012, 02:33 AM
  4. Create Dropdown menu without using the Validation on the Data Menu
    By lostinformulas in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-13-2006, 03:47 PM
  5. Drop down menu-short list the menu
    By Ron in forum Excel General
    Replies: 3
    Last Post: 03-04-2005, 07:06 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