Dependent Dropdowns

Hello,

I am trying to create a dependent drop down. I have searched around and used multiple examples and have came close but still end up with the duplicate values in the second drop down.

Hopefully I can make this clear enough what I am trying to do.

So this is the data I am working with;

Data.PNG

Here is the drop downs I have;

Drop Downs.PNG

So what I am trying to do is have each Year / Make / Model / Bed size drilled down from each of the previous drop downs.

For example only the 2020 Year would show a Make of GMC and Chevy and a Model of Colorado,Silverado, and Canyon.

Hopefully that makes sense.

I am using Excel 2016.

Why not use slicer?

If you select year, then all other slicer will show only values available for that year.

If you need dropdown. You can easily create it by using hidden slicer sheet, and linking it to filter field only pivot table(s).

Alternately, you can do it via formula based list sheet set up.


If you need more help. I'd recommend uploading sample file and not just the image.

To upload, use "Go Advanced" button, follow "Manage Attachments" hyperlink. It will open new tab/window for managing uploads.

Ah, I see.You want to use your selection in calculation. That's bit difficult to do with slicer, unless you use OLAP based pivot tables and use CUBE functions.

I'll see if I have time to whip up formula based one.

Here you go.

Notes:
1. This is one directional. I.E. dependency can only flow from Year -> Make -> Model -> Size. And removing Year, once you select Make, will not invalidate selection already made in Make etc.
2. I sorted your Data based on Year. To keep formula relatively simple and to show year dropdown from earliest to latest.

To extract distinct year values:
=IFERROR(LOOKUP(2,1/(COUNTIF($A$1:A1,Table13[Year])=0),Table13[Year]),"")

To extract distinct make, based on year. (If year isn't selected, it will show all available make)
=IFERROR(IF(Templates!$A$2="",LOOKUP(2,1/((COUNTIF($C$1:C1,Table13[Make])=0)),Table13[Make]),LOOKUP(2,1/((COUNTIF($C$1:C1,Table13[Make])=0)*(Table13[Year]=Templates!$A$2)),Table13[Make])),"")

Same principle applies to rest of lists.

Then dynamic named ranges added and used to supply list to data validation.

See attached.

You are welcome and thanks for the rep