Mar 27 2018
08:11 AM
- last edited on
Jul 25 2018
11:29 AM
by
TechCommunityAP
Mar 27 2018
08:11 AM
- last edited on
Jul 25 2018
11:29 AM
by
TechCommunityAP
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!
Mar 27 2018 09:09 AM
Mar 27 2018 09:13 AM - edited Mar 27 2018 09:17 AM
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.
Mar 27 2018 09:38 AM
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
Mar 27 2018 10:07 AM
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.
Mar 28 2018 02:58 PM - edited Mar 28 2018 02:59 PM
Amazing thanks! I can’t see the formulas you’ve used, please can you tell me how to achieve this?
Mar 28 2018 03:36 PM
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
Jan 18 2021 09:06 AM
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