Dependent Drop Down Lists

[Deleted]

Hey - please can someone advice me of the best way to create a database in the following way 

I would like to use three drop down lists, each dependent on the one before it. 

The first drop down list should be named 'Positions'. The user will be required to select from a number of positions, i.e. 'position 1', 'position 2', 'position 3', etc. 

Based on the role selected in the first drop down list, I would like to have the second drop down list then automatically display all the people who work in that role, i.e. 'person 1', 'person 2', 'person 3', etc.

Based on the person selected in the second drop down list, what I would like to have is a third drop down list with categories such as 'email', 'phone number' and 'skills', which when selected displays the selected persons information, i.e. their email address, phone number, skills, etc.

I hope this all makes sense and will be very grateful for any help! 

[#2]

There is an excellent toturial on dependent Validation lists here: http://www.contextures.com/xlDataVal02.html

[#3]

I recommend using Excel Tables that has dynamic range feature. Excel Table is available in Excel 2007, 2010, 2013, 2016, 365

Please see attached workbook.

Edit: attached workbook.

[#4]

Thanks so much for this - the Excel tables with dynamic range feature is just what I'm looking for. 

Do you know if it's possible to hide results until selections have been made?

E.g.

1) In the first drop down list Select 'Role 1'

2) In the second drop down list select 'Person 1' 

3) In the third drop down list select 'Email' 

4) Only result displayed is the email address of Person 1

Thanks again

[#5]

Please see attached.

with your additional criteria you need three dropdowns and one cell to return the details based on the three selections of dropdowns, if the dropdown data is selected incorrectly then it will show a text in that yellow cell.

[#6]

Amazing thanks! I can’t see the formulas you’ve used, please can you tell me how to achieve this?

[#7]

You are welcome.

The important formulas are placed inside the Data-Validation and are the following formulas.

The reason to use INDIRECT in D2 and G2 is, because when referring to Excel Table Inside the Data Validation, unlike traditional named range, Excel Table Structural references require to be wrapped inside INDIRECT("TableRef") otherwise DataValidation will not accept that as a valid reference.

Excel 2016 (Windows) 64 bit

	A	B
7	Address	Formula1
8	A2	=INDIRECT("tblCategory[Category]")
9	D2	=OFFSET(INDIRECT("tblType[#Headers]"),MATCH(A2,INDIRECT("tblType[Category]"),0),1,COUNTIF(INDIRECT("tblType[Category]"),A2),1)
10	G2	=OFFSET(INDIRECT("tblType2[#Headers]"),MATCH(D2,INDIRECT("tblType2[Category]"),0),2,COUNTIF(INDIRECT("tblType2[Category]"),D2),1)

Dropdowns

and the formulas used inside cells are the below formulas.   only formula in H2 is important, the other hyperlink formulas are not important. those three hyperlink formulas are used just to change mouse cursor to Hand.  

Excel 2016 (Windows) 64 bit

	A	B
7	Address	Formula
8	B2	=HYPERLINK("#"&ADDRESS(ROW(),COLUMN()-1),CHAR(128))
9	E2	=HYPERLINK("#"&ADDRESS(ROW(),COLUMN()-1),CHAR(128))
10	H2	=HYPERLINK("#"&ADDRESS(ROW(),COLUMN()-1),CHAR(128))
11	J2	=IFERROR(LOOKUP(2,1/(tblType2[Main Cat]=$A$2)/(tblType2[Category]=$D$2)/(tblType2[Brand]=$G$2),tblType2[Column1]),"Error Data Does not exist in the table")

Dropdowns

[#8]

Hi,

The template I created by adding dependent drop-down lists on the userform may be useful for you.
There are 3 dependent drop-down lists and a textbox on the userform. In the first drop-down list, suppliers are listed, categories according to the selected supplier are listed, products according to the selected category are listed. Finally, the price of the selected product is shown in the textbox.

Source and sample file at : Excel dependent drop down lists

[#9]

@kadirguler 

Good work!  

[#10]

@Jamil Mohammad  

Thanks