Dependent Drop Down Lists

**DocAElstein** · 11-06-2020, 03:47 PM

Hello Siyab,
Your requirement seems to be split into 2 parts:
Drop Down Lists _1. _2.
and
Dependent Drop Down Lists_3. _4.

I assume you have little knowledge of drop downs in Excel, since the first 2 parts of your question _1. And _2. are very fundamental basic Excel Drop down list stuff.
I have almost no knowledge of such things, so my start point would likely be an internet ( google ) search:
https://www.google.com/search?q=how%...t%20in%20excel
https://www.google.com/search?q=how ... list in excel
There is a vast amount of information and tutorials, even in different languages:
https://www.google.com/search?q=how ...xcel in Arabic
https://www.google.com/search?q=how ...excel in Hindi

I followed some of the tutorials. I also let the macro recorder run whilst doing some exercises, to see if coding could help us. Here some extra notes I made : https://excelfox.com/forum/showthrea...ll=1#post15077

I will answer your question in two parts.
First we will do the basic stuff…..
Drop Down Lists
_1. Under "Social Competencies", the drop down list should contain all the headers from the sheet "Comments" (Color Code - Peach).
_ 2. Under "Please Choose", the drop down list should contain "Does Not Meet Expectation", "Meets Expectation", "Exceeds Expectation" (Color Code - Grey).

Data Validation – Drop down lists
It would appear that Drop down lists are part of , or have evolved from, a more complex subject, that subject being “Data Validation”
For our purposes, the significance of this is only that we will often come cross the word “validation” and associated words a lot.

Under "Social Competencies", the drop down list should contain all the headers from the sheet "Comments"
"Under “Please Choose", the drop down list should contain "Does Not Meet Expectation", "Meets Expectation", "Exceeds Expectation"

Manually:
I followed the information available on the internet, and initially manually made those two drop down lists, as in the uploaded file, Appraisal - Drop Down.xls

Using VBA coding:
Alternative to manually, we can make those drop down lists using VBA
https://excelfox.com/forum/showthrea...ll=1#post15083
Example: create drop down lists using VBA

Code:

Sub MakeFirstTwoDropDowns2()  '     https://excelfox.com/forum/showthread.php/2676-Dependent-Drop-Down-Lists?p=15071#post15071
Rem 1 worksheets info
Dim WsApp As Worksheet, WsComs As Worksheet
 Set WsApp = ThisWorkbook.Worksheets("Appraisal"): Set WsComs = ThisWorkbook.Worksheets("Comments")
Rem 2 FirstTwoDropDowns2
 WsApp.Range("A2:A8").Validation.Delete
 'WsApp.Range("A2:A8").Validation.Add Type:=xlValidateList, Formula1:="Communicating Effectively,Resolving Conflict,Sharing Information,Supporting Co - workers"
 WsApp.Range("A2:A8").Validation.Add Type:=xlValidateList, Formula1:="" & WsComs.Range("A1").Value & "," & WsComs.Range("A11").Value & "," & WsComs.Range("A21").Value & "," & WsComs.Range("A31").Value & ""

 WsComs.Range("C2:C8").Validation.Delete
 'WsComs.Range("C2:C8").Validation.Add Type:=xlValidateList, Formula1:="Does Not Meet Expectation,Meets Expectation,Exceeds Expectation"
 WsComs.Range("C2:C8").Validation.Add Type:=xlValidateList, Formula1:="" & WsComs.Range("A2").Value & "," & WsComs.Range("B2").Value & "," & WsComs.Range("C2").Value & ""
End Sub

Dependent Drop Down Lists

_3. If a person selects "Communicating Effectively" under Social Competencies column, and then selects "Meets Expectation" in the next column, then the drop down list on Column D "Please Choose" should display the list from B3:B8 from the sheet named "Comments".
Another example would be if a person selects "Resolving Conflict" under Social Competencies column, and then selects "Does Not Meet Expectation" in the next column, then the drop down list on Column D "Please Choose" should display the list from A13:A18 from the sheet named "Comments".
_4. If a person selects "Sharing Information" under Social Competencies column, then the drop down list on Column E "Give Advise" should display the list from A28:A32 from the sheet named "Give Advise"

One way to do this would be to use event coding which triggers macros similar to those discussed above which will create the required drop down lists.
( A recent thread example is here: https://excelfox.com/forum/showthrea...nditions/page2 )

I suggest, Siyab, that you study
_ what I have done for you so far, for _1. And _2.
_ this thread: https://excelfox.com/forum/showthrea...nditions/page2

See if you can make a start yourself on _3. And _4

I will look again in a day or two to see if you need further help.

This would be my initial idea…….

Second part: Dependant drop down list.

The contents of the third and forth drop down lists ( in column D and E in worksheet Appraisals ) are dependant on the selection in the first two drop downs list . ( in column A and C ).

One way to tackle this is to re create the drop down lists using VBA for column C and column D every time a value is changed in column A or column B
We have seen in the previous posts how to create a drop down list using VBA .
So all we need is similar coding, but which is triggered by a worksheets change event

Alan