+ Reply to Thread
Results 1 to 6 of 6

Multiple dependent drop down lists

  1. #1
    Registered User
    Join Date
    11-20-2020
    Location
    California
    MS-Off Ver
    WIndows
    Posts
    18

    Multiple dependent drop down lists

    Hi, Trying to figure out the last portion of my spreadsheet and am having some trouble. I am trying to add drop down lists based on the selection of another drop down list. They are as follows:


    -Sheet 1: Customer city (Column B) dependent on selection of Column A selection. Data on Sheet 3 Columns A,B &C.

    Here is the really tricky one for me.
    -Sheet1: Equipment Type (Column D) dependent on selection of Column C selection, then to have Column G & I auto populate with their corresponding data based off of the Column D selection.
    Data on Sheet 3 Columns E,F,H&J.


    Sorry if this sounds confusing. Let me know if I can be a bit clearer.

    Thanks so much for any help, it is much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Multiple dependent drop down lists

    I renamed some of your table ranges in order to use them in formulas with the INDIRECT function, for example, Customer_1 for the cities under that customer.
    For B column, this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I use SUBSTITUTE because space cannot be part of a named range title, so I replace space with underline(_) to make the text in A3 (Customer 1) read (Customer_1) instead, thus matching respective named range.

    For the trades ranges no substitution was required, so I use this formula in column D:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For Opp life and Budget, I combine INDIRECT & RIGHT functions, like in this formula in column G:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The RIGHT function picks the key characters from the column headings, such as "life" and "budget" to build desired named range with the INDIRECT function.

    Please check file and let us know how it goes.

    Good luck!
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-20-2020
    Location
    California
    MS-Off Ver
    WIndows
    Posts
    18

    Re: Multiple dependent drop down lists

    Thanks so much Estevaoba! I do have additional questions if you don't mind. I think I should have been a bit more clear on what I was trying to achieve, and I made it a bit confusing.

    Ultimately what I am trying achieve is to give individual lines in those columns on Sheet 1 the ability to have its own selections based on the previous line. For example:

    When I choose Customer 1 on Sheet1 A3, B2 should populate a drop down list of corresponding customer city found on the data sheet. As of now with your formulas, when I select Customer 1, all of the cities for that corresponding customer auto populate down the entire column.

    The same principal should apply for the Column C. When I select a building system C3, the corresponding equipment types should show in a drop down list on D3.

    I hope this makes sense. Sorry for the extra inquiry and thanks again, I really appreciate your help!

  4. #4
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Multiple dependent drop down lists

    You're welcome. Glad to help.

    For data validation in B3, this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that I lock column $A only, so that when you copy down, data validation formula in B4 will be
    =INDIRECT(SUBSTITUTE($A4," ","_")) and so on, assuming a different customer may be entered in A4.

    For data validation in D3:
    =INDIRECT($C3), same as above, column locked only.

    Formulas in columns G & I are a bit different now, so as to match selection in column D:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Revised file attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-20-2020
    Location
    California
    MS-Off Ver
    WIndows
    Posts
    18

    Re: Multiple dependent drop down lists

    Amazing. I am entering these into my notes. I am very thankful! Wishing you happy Holidays and a prosperous New year!

  6. #6
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Multiple dependent drop down lists

    You're welcome.
    Thank you for the feedback and for the reputation added.
    Also wishing you and yours Happy Holidays and a Happy New Year!

+ 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: 4
    Last Post: 01-27-2020, 09:32 AM
  2. How to Create Multiple Dependent Drop Down Lists?
    By Dieterl in forum Excel General
    Replies: 10
    Last Post: 10-30-2018, 02:36 PM
  3. Multiple Dependent Drop down lists to generate data
    By Acousticlife81 in forum Excel General
    Replies: 1
    Last Post: 04-20-2017, 03:58 PM
  4. [SOLVED] Help with multiple dependent drop down lists
    By NHamilton07 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-28-2014, 07:58 AM
  5. Defaults for Multiple Dependent Drop Down Lists in same Worksheet
    By KyleW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2010, 03:39 PM
  6. Dependent Drop Down lists (multiple drop down)
    By jijy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2007, 09:56 AM
  7. [SOLVED] Multiple Dependent Drop Down lists?
    By Steve in forum Excel General
    Replies: 0
    Last Post: 12-07-2005, 04:40 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