PDA

View Full Version : HELP with Dependant Drop Down list for multiple selections



tina.tjoeng
2017-03-06, 01:02 AM
Hi All,

Thank you in advance for your assistance. I'm after a dependant drop down list for more than 2 dependencies in the sample data provided 6526

I have the following:
Data_Entry Tab: Where information is keyed in
Course List Tab: List of Courses and course Numbers
Learner List Tab: List of Learners and their user IDs

Concept:
In the Data_Entry Tab:

The aim is to eliminate as much manually keying in of information, so it would be to create dependant drop down lists that would narrow down values in the following drop down lists selected. eg. Division, you'd have a select of HSSE, External, Mechanical, Operations etc, then from there, one would select Proficiency Level of Knowledge, Interim Skill or Skill, then from there you would select the unit name that corresponds to that particular division and proficiency, eg. Forklift is a HRL proficiency and would be a course only found under External division etc. (I hope that made sense)
I figured it out for 1 dependency but was not able to figure out how to do this with 3 pieces of information that were dependant on eachother to get the right value
Then in Course Code and Module Code field, the course and proficiency selected needs to retrieve the corresponding course and module Codes


Learner List:


This is in a different format to how it is entered into the Data_Entry Tab, so would need a formula in the User ID column in Data_Entry Tab to retrieve the corresponding user ID, Learner List will be upldated by system report which has full name in that format (would like to reduce the amount of formatting and cutting/changing of data required)


Apologies, lastly, I'll need to include conditional formatting or some sort of indicator if a completion record of the same course and completion date by learner is entered in twice?

Hope someone will be able to help

Thank you

T

NBVC
2017-03-06, 01:52 PM
Please see attached.

1. Create 2 separate lists one lists the divisions across the top with the respective proficiency levels below and one that lists the unique proficiency levels across the top with respective Unit names below.
2. Add Data Validation for Divisions as >> List >> ='Course List'!$I$1:$O$1 (i.e. point to division headers).
3. Add Data Validation for Proficiency levels >> List >> =OFFSET(INDEX('Course List'!$I$1:$O$1,MATCH(B2,'Course List'!$I$1:$O$1,0)),1,,COUNTA(INDEX('Course List'!$I:$O,0,MATCH(B2,'Course List'!$I$1:$O$1,0)))-1,1)
4. Add Data Validation fro Unit Names >> List >> =OFFSET(INDEX('Course List'!$Q$1:$U$1,MATCH(E2,'Course List'!$Q$1:$U$1,0)),1,,COUNTA(INDEX('Course List'!$Q:$U,0,MATCH(E2,'Course List'!$Q$1:$U$1,0)))-1,1)
5. Formula for User ID: =INDEX('Learner List'!$B$2:$B$11,MATCH(D2&", "&C2,'Learner List'!$A$2:$A$11,0)) copied down
6. Formula for Course Code: =INDEX('Course List'!D$2: D$22,MATCH(1,INDEX(('Course List'!$A$2:$A$22=$F2)*('Course List'!$B$2:$B$22=$E2)*('Course List'!$C$2:$C$22=$B2),0),0)) copied to next column and then down
7. Conditional formatting for duplicates: in column J >> user formula to determine which cells to format >> =COUNTIFS($G$2:$G$22,$G2,$H$2:$H$22,$H2,$I$2:$I$22,$I2,$J$2:$J$22,$J2)>1 >> Click Format and choose from Fill tab. Change Countifs criteria ranges/criteria to suit what you are interested in.

tina.tjoeng
2017-03-06, 11:57 PM
Please see attached.

1. Create 2 separate lists one lists the divisions across the top with the respective proficiency levels below and one that lists the unique proficiency levels across the top with respective Unit names below.
2. Add Data Validation for Divisions as >> List >> ='Course List'!$I$1:$O$1 (i.e. point to division headers).
3. Add Data Validation for Proficiency levels >> List >> =OFFSET(INDEX('Course List'!$I$1:$O$1,MATCH(B2,'Course List'!$I$1:$O$1,0)),1,,COUNTA(INDEX('Course List'!$I:$O,0,MATCH(B2,'Course List'!$I$1:$O$1,0)))-1,1)
4. Add Data Validation fro Unit Names >> List >> =OFFSET(INDEX('Course List'!$Q$1:$U$1,MATCH(E2,'Course List'!$Q$1:$U$1,0)),1,,COUNTA(INDEX('Course List'!$Q:$U,0,MATCH(E2,'Course List'!$Q$1:$U$1,0)))-1,1)
5. Formula for User ID: =INDEX('Learner List'!$B$2:$B$11,MATCH(D2&", "&C2,'Learner List'!$A$2:$A$11,0)) copied down
6. Formula for Course Code: =INDEX('Course List'!D$2: D$22,MATCH(1,INDEX(('Course List'!$A$2:$A$22=$F2)*('Course List'!$B$2:$B$22=$E2)*('Course List'!$C$2:$C$22=$B2),0),0)) copied to next column and then down
7. Conditional formatting for duplicates: in column J >> user formula to determine which cells to format >> =COUNTIFS($G$2:$G$22,$G2,$H$2:$H$22,$H2,$I$2:$I$22,$I2,$J$2:$J$22,$J2)>1 >> Click Format and choose from Fill tab. Change Countifs criteria ranges/criteria to suit what you are interested in.

Thank you so much NBVC! That's so good, is there a possibility of having the drop down lists formatted in a way where one is able to use predictive text?

NBVC
2017-03-07, 01:01 PM
not easily....

Take a look here for a non-macro solution:

http://www.ozgrid.com/Excel/autocomplete-validation.htm

or here for a macro solution

http://www.contextures.com/xlDataVal10.html

tina.tjoeng
2017-03-07, 10:39 PM
not easily....

Take a look here for a non-macro solution:

http://www.ozgrid.com/Excel/autocomplete-validation.htm

or here for a macro solution

http://www.contextures.com/xlDataVal10.html

Thank you NBVC for the resource! I'll give it a go