Dropdown lists that are dependant on each other

[Angjo]

Hello all!
Really hope you can help. I need to make a drop down list that is dependant on another.
I have one sheet that looks like this:

What i would love to be able is to select a country and then have a second drop down that will only bring back the list, dependant on the first choice like follows:

So if you pick Afghanistan you get colours and if you pick Albania you get a list of material types etc.

I really hope this is possible and any help would be really appreciated!

Thanks!

 

[HighAndWilder]

Hello all!
Really hope you can help. I need to make a drop down list that is dependant on another.
I have one sheet that looks like this:
View attachment 120178

What i would love to be able is to select a country and then have a second drop down that will only bring back the list, dependant on the first choice like follows:
View attachment 120179
So if you pick Afghanistan you get colours and if you pick Albania you get a list of material types etc.

I really hope this is possible and any help would be really appreciated!

Thanks!

What version of Office are you using as it can determine what solution options are available?

 

[Angjo]

@HighAndWilder thanks for your reply, its Version 16.89.1 (24091630). Only issue is solution cant be macro based due to restrictions on laptop.

 

[SunnyAlv]

Hii

try with Data Validation to create DropDown List, like :

Data -> Data Validation (Shortcut : alt + a + v + v), and follow image below, change source to 'Country' List

to show list 2 based on 'Country' List u can using #Filter Formula, like

=FILTER(H6:H14,G6:G14=J6)

 

[Angjo]

Thank you @SunnyAlv but when i try this i get a #SPILL! error. Any ideas on why?

 

[Angjo]

the tool tip says that spill range isnt blank

 

[Angjo]

Ahhh ok i fixed it but that doesnt help im afraid. I dont want to show a list dependant on a dropdown, i want to show another dropdown.

 

[MARK858]

Have a look at the links below

Excel Dependent Drop Down Lists from Sorted Table OFFSET

Limit choices in a data validation drop down list, based on selection in another cell. Example based on sorted list of regions and customers

www.contextures.com

How to make dependent dropdown lists in Excel

One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. But how can you make one dropdown dynamically respond to another? In other words, how can you make the values in a dropdown list depend on another...

exceljet.net

P.S. You really should update the Office version in your profile

 

[Alex Blakenburg]

I agree with Mark please update your profile to show which version of Excel you are using. I believe you have MS 365.
Mark Proctor & Wyn Hopkins have been having some friendly rivalry on having the simplest dependent dropdown list see below:

 

[Angjo]

Thanks @Alex Blakenburg but neither of those seem to work. The first one is saying to use the formula =t[1] which produces an invalid cell reference and the second video talks of =DDL functions which again my version of excel doesnt seem to accept. Im not sure where im going wrong