+ Reply to Thread
Results 1 to 9 of 9

Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Relevant

  1. #1
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Relevant

    Hello,

    I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.

    I have attached a file with sample data which has the following format.

    Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)

    Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)

    Column 3 = vehicle colour available for vehicle package option

    Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)

    Also please note that Column 3 values of colour options vary for each 'vehicle package option'.

    The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.

    Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming but I am open to suggestions.

    Thank you for your help and suggestions.

    TV

    Sample Data - Dependent Drop Down Cells Question.xlsx

  2. #2
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Rele

    Is this not possible or too difficult? Would it be much simplified if it's reduced to 2 associated drop down lists? Just wondering...

    TV

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Rele

    Is your sheet going to be set up like your example except with 100 different models?
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Rele

    Yes, exactly.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Rele

    Okay, wanted to stay away from arrayed formulas so it doesn't slow down your sheet.

    In Sheet1
    In D is a dummy column which is used to pull out the packages for each model car. In D2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In E is concatenation of A and B which is used to pull a range for Color
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In F, I created a list of models. In Excel 2007, you can copy Col A to Col F and then from the Data Tab of the ribbon, delete duplicates.
    In G I created a list of packages for whatever model is chosen in Sheet2!A2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    All of these columns can be hidden.
    Then I created Dynamic Defined Names (Formula Tab> Name Manager)
    Models =
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Package =
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Colour =
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then set data validation for sheet2 A2,B2,C2 to = models, package, and colour.
    See example.
    Questions?

  6. #6
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Here
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Rele

    ChemistB,

    I was expecting a bunch of VBA code, so it is pretty cool and amazing you made it work without it. I'm assuming this should work just as well with 2 workbooks where all source data will be in one workbook and the drop down list will be in another.

    There are a couple of subtle quirks. When selections in the three drop down lists have been made moving left to right, backtracking and changing a value in the vehicle package option allows the colour choice to remain as it was set even if that colour choice is not available for that vehicle package option. Might there be a way to blank out a value in a list box when the value in the combo box to it's left is being altered?

    Also, the drop down lists work in row 2 on sheet 2 where you placed them. If I copy and paste the row to another location the dynamic relationship no longer works.

    Thanks so much for pulling this together. It was really neat to see it done without VBA. In my mind I'm trying to see how it might come together via VBA as well.

    Cheers,

    TV

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Rele

    For that you do need VBA code. Right click on the sheet tab > View Code
    and paste this code in. (Line 3 assumes your first dropdown is in A2, Line 6 assumes your second dropdown is in B2)
    Please Login or Register  to view this content.
    Questions?

  8. #8
    Registered User
    Join Date
    02-11-2014
    Location
    Portugal
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Rele

    Hi, i'm inexperienced in excel and need something similar to this example, but didn't understand it well.
    In the layout sheet need four dependent droplists for items, Classe, Designação, Tipo, Dimensões to determine mass and painting area from Materiais Sheet. Have figured a way to return the values, but don't know how to develop a 4 Level dependent drop list with unique values.

    Best Regards,

    Jorge
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-11-2014
    Location
    Portugal
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Dependent Drop Down List - 3 Levels - Remove Common Entries From Selection - Show Rele

    This is very similar to what i need, but not in a userform. In cells Sheet.Layout B10:E10, same for B11:E11, etc until B31:E31.

    Would be very thankful if anyone could help me.

    http://www.excelforum.com/excel-prog...-userform.html
    Last edited by jomacho; 02-12-2014 at 05:21 PM.

+ 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. [SOLVED] Code to put today's date in empty cell dependent on selection from drop-down list
    By vickyh1 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-28-2013, 09:09 AM
  2. Replies: 4
    Last Post: 05-28-2013, 06:06 AM
  3. Create a sheet with content dependent on the selection of a drop-down list
    By bbaumgardner22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 04:25 PM
  4. Drop Down List, numeric output dependent on selection
    By Archibald_SM in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-24-2013, 06:44 PM
  5. Replies: 5
    Last Post: 01-28-2011, 05:31 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