+ Reply to Thread
Results 1 to 7 of 7

Two Dependent and Dynamic Drop Down Lists

  1. #1
    Registered User
    Join Date
    06-03-2015
    Location
    US
    MS-Off Ver
    NEw
    Posts
    22

    Two Dependent and Dynamic Drop Down Lists

    Hi Everyone,

    I am trying to create a tool with the following functions:

    1) Dynamic Drop Down list of Locations
    2) Dynamic Drop Down List of Resin Types
    3) Automatic Generation of Resin price

    I intend for the user to select a location, which will then limit the resin options in the 2nd drop down, and based on both selections will generate a price in a separate cell.

    The tool has to be easily updated so please no macros. Also, I can only work with the format of the attached file.

    I've gone on another forum and no one could do this without changing my format. Your expertise would be amazing!

    -MK
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Two Dependent and Dynamic Drop Down Lists

    Hi marcelkahn,

    See the attached file. It should be doing what you've asked for. I've had to make some minor changes to the file.

    1)I've created a dynamic named range for locations called "List_Locations"
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    2) Created a another table in Locations sheet that picks up the appropriate Resin labels according to the selected locations using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    3) Then, in the same manner as (1) above, I've created a named range for resins "List_Resins"
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    4) Finally, to pull up the relevant price, I've used the following INDEX MATCH function.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-03-2015
    Location
    US
    MS-Off Ver
    NEw
    Posts
    22

    Re: Two Dependent and Dynamic Drop Down Lists

    Hi Quekbc,

    This is great, but would you be able to achieve the same results without changing my original format at all? This is what I've been trying to achieve and have not found a way to do it. You're a Rockstar.
    Thank you for putting in the time!

    -MK

  4. #4
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Two Dependent and Dynamic Drop Down Lists

    Right MK. Got it running today. Did pretty much the same thing with the names, but without changing or adding any other formatting to the spreadsheet.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-03-2015
    Location
    US
    MS-Off Ver
    NEw
    Posts
    22

    Re: Two Dependent and Dynamic Drop Down Lists

    Quekbc,

    WOAH. You are amazing! I've never seen anyone work excel the way you just did! THANK YOU!!!

    -MK

  6. #6
    Registered User
    Join Date
    06-03-2015
    Location
    US
    MS-Off Ver
    NEw
    Posts
    22

    Re: Two Dependent and Dynamic Drop Down Lists

    Quekcb,

    Is it possible to make the dropdown lists start off with blank options and is there any way to automatically make the Resin drop down go blank every time a new location is selected?
    I'm really amazed by your mastery of excel!

    -MK

  7. #7
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Two Dependent and Dynamic Drop Down Lists

    Yes you can. But you will need VBA to do it. Open up your file, press Alt F11, then on the left pane of the VBA window, you'll find a Microsoft Excel Objects window and look for something that says Sheet1 (TOOL). Double click on that and type in the following code.

    Also remember to save your file as a Macro Enabled workbook (XLSM).

    Or you can refer to the attached file here.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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: 11
    Last Post: 10-08-2021, 04:55 AM
  2. [SOLVED] Make 2 dependent drop down lists dynamic
    By arsene2conde in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2013, 02:57 PM
  3. Dependent drop down lists without creating unique named lists
    By pajordan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 12:20 PM
  4. [SOLVED] Dependent drop down lists- dynamic data range- excluding Headers
    By strud in forum Excel General
    Replies: 3
    Last Post: 05-28-2013, 04:10 AM
  5. [SOLVED] Dependent drop down lists based on dynamic data range
    By strud in forum Excel General
    Replies: 15
    Last Post: 04-19-2013, 08:08 AM
  6. [SOLVED] Column B drop down list with 2 options, columns C & E with dependent drop down lists
    By betic in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2012, 03:47 PM
  7. Dependent and dynamic drop-down lists
    By csunseri in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-24-2011, 04:32 AM

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