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

**tv69** · 12-04-2013, 11:31 AM

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

**tv69** · 12-17-2013, 11:27 AM

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

**ChemistB** · 12-17-2013, 11:31 AM

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

**tv69** · 12-17-2013, 11:35 AM

Yes, exactly.

**ChemistB** · 12-17-2013, 01:03 PM

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:

=IF(B2=B1, "",A2&"_"&COUNTIF($D$1:D1,A2&"*")+1)

In E is concatenation of A and B which is used to pull a range for Color

Formula:

=A2&B2

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:

=IFERROR(INDEX($B$2:$B$500,MATCH(Sheet2!$A$2&"_"&ROWS($A$1:A1),$D$2:$D$500,0)),"")

All of these columns can be hidden.
Then I created Dynamic Defined Names (Formula Tab> Name Manager)
Models =

Formula:

=Sheet1!$F$2: (INDEX(Sheet1!$F$2:$F$500, COUNTA(Sheet1!$F$2:$F$500)))

Package =

Formula:

=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$500, SUMPRODUCT(--(LEN(Sheet1!$G$2:$G$500)>0)))

Colour =

Formula:

=INDEX(Sheet1!$C$2:$C$500,MATCH(Sheet2!$A$2&Sheet2!$B$2,Sheet1!$E$2:$E$500,0)):INDEX(Sheet1!$C$2:$C$500, LOOKUP(2, 1/(Sheet1!$E$2:$E$500=Sheet2!$A$2&Sheet2!$B$2),ROW(Sheet1!$C$2:$C$500)-1))

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

**tv69** · 01-06-2014, 01:26 AM

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

**ChemistB** · 01-06-2014, 09:28 AM

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)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2")) Is Nothing Then
    Target.Range("B1:C1").ClearContents
End If
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Target.Offset(0, 1).ClearContents
End If
End Sub

Questions?

**jomacho** · 02-11-2014, 06:39 PM

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

**jomacho** · 02-12-2014, 05:16 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Code to put today's date in empty cell dependent on selection from drop-down list

Coming up with a total which is dependent on the selection a drop down list

Create a sheet with content dependent on the selection of a drop-down list

Drop Down List, numeric output dependent on selection

How to show a value dependent on the value shown in a drop down list?

Bookmarks

Bookmarks

Posting Permissions