Setting up dependent dropdown lists in Excel using formulas

[Deutz]

Hi and thanks in advance,

I am trying to set up 2 Excel dropdown lists, one dependent on the other, using formulas if possible. I have looked over a lot of suggestions on the web but not found exactly the scenario I need.

Here is an example of the data the lists will be based on:

Country	Year:​	2018​	2019​	2020​
Australia		1	2	3
Czech Republic			88.12	14
Venezuala			7
Switzerland		2.2		44
Norway		3.45		55
USA			100

...

So the 1st dropdown list contains the years: 2018, 2019, 2020

When a year is selected in the 1st dropdown list, it should load the 2nd dropdown list with all the countries that have a value in the selected year column.

For instance, if 2018 was selected in the 1st dropdown list then load the following countries in the 2nd dropdown list: Australia, Switzerland, Norway

When the user makes a selection in the 1st dropdown list I would also like to clear any visible value in the 2nd dropdown llst.

Thanks again
Deutz

 

[Peter_SSs]

The possible solutions depend on your excel version. I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

 

[Deutz]

Thanks for the reminder to update my Account details. I am using Office 365 on Windows.

 

[Peter_SSs]

Thanks for the profile update. Excel 365 (provided you have the FILTER function) makes the second drop-down very easy.

Use a spare column (I have used col G) which can be hidden once the formula is added.
The G2 formula needs to be entered in that cell only and the other results will automatically 'spill' down to other rows as required.
The Data validation formulas are shown below my sheet.

To clear the second cell when the first is altered requires vba. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("J1")) Is Nothing Then Range("J2").ClearContents
End Sub

Deutz 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Country	Year:	2018	2019	2020				Year	2018
2	Australia		1	2	3		Australia		Country
3	Czech Republic			88.12	14		Switzerland
4	Venezuala			7			Norway
5	Switzerland		2.2		44
6	Norway		3.45		55
7	USA			100
8
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=IF(J1="","Select Year First",FILTER(A2:A7,INDEX(C2:E7,0,MATCH(J1,C1:E1,0))<>""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J1	List	=$C$1:$E$1
J2	List	=G2#

 

[Deutz]

Thanks for the profile update. Excel 365 (provided you have the FILTER function) makes the second drop-down very easy.

Use a spare column (I have used col G) which can be hidden once the formula is added.
The G2 formula needs to be entered in that cell only and the other results will automatically 'spill' down to other rows as required.
The Data validation formulas are shown below my sheet.

To clear the second cell when the first is altered requires vba. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("J1")) Is Nothing Then Range("J2").ClearContents
End Sub

Deutz 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Country	Year:	2018	2019	2020				Year	2018
2	Australia		1	2	3		Australia		Country
3	Czech Republic			88.12	14		Switzerland
4	Venezuala			7			Norway
5	Switzerland		2.2		44
6	Norway		3.45		55
7	USA			100
8
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=IF(J1="","Select Year First",FILTER(A2:A7,INDEX(C2:E7,0,MATCH(J1,C1:E1,0))<>""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J1	List	=$C$1:$E$1
J2	List	=G2#

View attachment 21167

Thanks so much for your solution Peter, however I don't have access to the FILTER function as is says it is invalid when I try to use it. I do have Office 365 installed though.

 

[Peter_SSs]

I don't have access to the FILTER function as is says it is invalid when I try to use it. I do have Office 365 installed though.

Not all 365 subscribers have all the functions yet. It shouldn't be too long though (I hope ?)

Until then, you can try this, with a similar setup but not such simple formulas/validation. You still need the vba to do the clear.
G2 formula is copied down as many rows as the country list in column A.

Deutz 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Country	Year:	2018	2019	2020		4		Year	2019
2	Australia		1	2	3		Australia		Country
3	Czech Republic			88.12	14		Czech Republic
4	Venezuala			7			Venezuala
5	Switzerland		2.2		44		USA
6	Norway		3.45		55
7	USA			100
Sheet3

Cell Formulas
Range	Formula
G1	G1	=COUNTIF(G2:G7,"?*")
G2:G7	G2	=IF(AND(J$1="",ROWS(G$2:G2)=1),"Select Year First",IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$7)/(INDEX(C$2:E$7,0,MATCH(J$1,C$1:E$1,0))<>""),ROWS(G$2:G2))),""))

Cells with Data Validation
Cell	Allow	Criteria
J1	List	=$C$1:$E$1
J2	List	=OFFSET(G2,,,G1)

 

[Deutz]

Not all 365 subscribers have all the functions yet. It shouldn't be too long though (I hope ?)

Until then, you can try this, with a similar setup but not such simple formulas/validation. You still need the vba to do the clear.
G2 formula is copied down as many rows as the country list in column A.

Deutz 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Country	Year:	2018	2019	2020		4		Year	2019
2	Australia		1	2	3		Australia		Country
3	Czech Republic			88.12	14		Czech Republic
4	Venezuala			7			Venezuala
5	Switzerland		2.2		44		USA
6	Norway		3.45		55
7	USA			100
Sheet3

Cell Formulas
Range	Formula
G1	G1	=COUNTIF(G2:G7,"?*")
G2:G7	G2	=IF(AND(J$1="",ROWS(G$2:G2)=1),"Select Year First",IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$7)/(INDEX(C$2:E$7,0,MATCH(J$1,C$1:E$1,0))<>""),ROWS(G$2:G2))),""))

Cells with Data Validation
Cell	Allow	Criteria
J1	List	=$C$1:$E$1
J2	List	=OFFSET(G2,,,G1)

Thanks so much Peter. I will test this out tomorrow. Cheers

 

[Peter_SSs]

Thanks so much Peter. I will test this out tomorrow. Cheers