how to create dependent dropdown list in Excel

[m_vishal_c]

Hi,
i need to create dynamic dependant dropdown list in excel like below

Unique Aged Care Name	Aged Care Name	Doctor Name
A	B	John
B	B	william
C	A	Peter
D

Unique Aged Care Name is all Aged Care Names, Aged Care Name is dropdown to select Aged care.

I need dependant dropdown list like if I select B as Aged Care Name then "John", and "William" should come to another column

I just need to create in Excel not macro

Heaps thanks

 

[Toadstool]

Hi M_Vishal_C,

Like this?

M_Vishal_C.xlsx
	A	B	C	D	E	F	G	H
1	Unique Aged Care Name	Aged Care Name	Doctor Name		Select Aged Care Name	Select Doctor		LoV Build
2	A	B	John		B	william		John
3	B	B	william					william
4	C	A	Peter
5	D
6
7
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/($B$2:$B$9999=$E$2),ROW()-ROW($C$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$A$2:$A$5
F2	List	=OFFSET($H$2,,,COUNTIF($H$2:$H$999,"> "))

 

[m_vishal_c]

Hi M_Vishal_C,

Like this?

M_Vishal_C.xlsx
	A	B	C	D	E	F	G	H
1	Unique Aged Care Name	Aged Care Name	Doctor Name		Select Aged Care Name	Select Doctor		LoV Build
2	A	B	John		B	william		John
3	B	B	william					william
4	C	A	Peter
5	D
6
7
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/($B$2:$B$9999=$E$2),ROW()-ROW($C$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$A$2:$A$5
F2	List	=OFFSET($H$2,,,COUNTIF($H$2:$H$999,"> "))

Hi Thanks for supporting, this is exactly what I wanted. Heaps thanks

 

[Toadstool]

Hi Thanks for supporting, this is exactly what I wanted. Heaps thanks

You're welcome!
Thanks for the feedback.

 

[m_vishal_c]

Hi M_Vishal_C,

Like this?

M_Vishal_C.xlsx
	A	B	C	D	E	F	G	H
1	Unique Aged Care Name	Aged Care Name	Doctor Name		Select Aged Care Name	Select Doctor		LoV Build
2	A	B	John		B	william		John
3	B	B	william					william
4	C	A	Peter
5	D
6
7
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/($B$2:$B$9999=$E$2),ROW()-ROW($C$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$A$2:$A$5
F2	List	=OFFSET($H$2,,,COUNTIF($H$2:$H$999,"> "))

Hi Mate, can i ask something that formula i applied on F2 it works good but it does not work on F3. i.e if I select E3=A (Aged care) then F3 should show "Peter" but it shows "John" and "William". Please suggest

 

[m_vishal_c]

Hi M_Vishal_C,

Like this?

M_Vishal_C.xlsx
	A	B	C	D	E	F	G	H
1	Unique Aged Care Name	Aged Care Name	Doctor Name		Select Aged Care Name	Select Doctor		LoV Build
2	A	B	John		B	william		John
3	B	B	william					william
4	C	A	Peter
5	D
6
7
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/($B$2:$B$9999=$E$2),ROW()-ROW($C$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$A$2:$A$5
F2	List	=OFFSET($H$2,,,COUNTIF($H$2:$H$999,"> "))

Hi Mate, can i ask something that formula i applied on F2 it works good but it does not work on F3. i.e if I select E3=A (Aged care) then F3 should show "Peter" but it shows "John" and "William". Please suggest

 

[Toadstool]

If you want to have multiple rows of selection options then you'll need to have multiple LoV Builds. This means you need to go horizontally for each row.

In this example I have allowed for up to 10 doctor names by building from column H to column Q. If you need more then you'll need to copy that formula further right and change the range in the Data Validation, List OFFSET.

M_Vishal_C-V2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Unique Aged Care Name	Aged Care Name	Doctor Name		Select Aged Care Name	Select Doctor		LoV Build
2	A	B	John		B	william		John	william
3	B	B	william		A	Peter		Peter	Tom
4	C	A	Peter		C	Jim		Jim
5	D	A	Tom
6		C	Jim
7
Sheet1

Cell Formulas
Range	Formula
H2:Q7	H2	=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/($B$2:$B$9999=$E2),COLUMN()-COLUMN($G$1)))&"","")

Cells with Data Validation
Cell	Allow	Criteria
E2:E10	List	=$A$2:$A$5
F2:F12	List	=OFFSET($H$1,ROW()-ROW($H$1),,,COUNTIF($H2:$Q2,"> "))

 

[m_vishal_c]

HI Mate, thanks for replying but in this case there are possibilities that may have more than 100 doctors but we dont know now.
so here it will not work

 

[Toadstool]

I dummied up 200 rows of data and Doctor names and carried the columns up to HE so more than 200 LoV entries (which could be built on another sheet) and it does work.

You don't have your Excel version in your profile so my Excel 2016 solution may have a cleaner solution in a later version, or you could ask for a VBA approach.

M_Vishal_C-V2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Unique Aged Care Name	Aged Care Name	Doctor Name		Select Aged Care Name	Select Doctor		LoV Build
2	A	B	John		A	Peter		Peter	Tom
3	B	B	william		B	william		John	william
4	C	A	Peter		C	Jim		Jim
5	D	A	Tom		X78	Bob-78		Bob-78
6	X6	C	Jim		X125	Peter-125		Bob	Peter-125
7	X7	X7	Harry		X101	John-101		John-101
8	X8	X8	Kim		X200	Harry-172		Harry-172
9	X9	X9	Sally		X144	Harry-73		william-69	Harry-73	Vanessa-142
10	X10	X10	Vanessa
11	X11	X11	Gary
12	X12	X125	Bob
13	X13	X13	John-13
14	X14	X14	william-14
V3

Cell Formulas
Range	Formula
H2:M14	H2	=IF($E2="","",IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/($B$2:$B$9999=$E2),COLUMN()-COLUMN($G$1)))&"",""))

Cells with Data Validation
Cell	Allow	Criteria
E2:E200	List	=$A$2:$A$200
F2:F200	List	=OFFSET($H$1,ROW()-ROW($H$1),,,COUNTIF($H2:$HE2,"> "))

 

[m_vishal_c]

HI Mate, i really appreciate your support but I found another way by Name range in Name Manager and on column F I applied "OFFSET(Aged_Care_Start,MATCH(E2,Aged_Care_Name_Drop_Down,0)-1,1,COUNTIF(Aged_Care_Name_Drop_Down,E2),1)
and in column E I applied this formula =IF(F2="",Unique_Aged_Care_Name,INDEX(Aged_Care_Name_Drop_Down,MATCH(F2,Doc_Name,0)))

and it worked. Again thanks a lot for your help