Dependent Drop Down Lists

[msiyab]

Hi All,

I am preparing an Appraisal file for my company. Wherein, I would like to have drop down lists based on the cell values of the previous cells.

3454

The 'Appraisal' sheet is where I would like to have all the drop down lists.

Requirements:

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).

3. So, 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. So, 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"


Would highly appreciate it if some one could really guide me through the process of Data Validation for the above-mentioned exercise.


Regards,
Siyab

[DocAElstein]

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%20to%20add%20create%20drop%20down%20l ist%20in%20excel
https://www.google.com/search?q=how to add create drop down list in excel
There is a vast amount of information and tutorials, even in different languages:
https://www.google.com/search?q=how to add create drop down list in excel in Arabic
https://www.google.com/search?q=how to add create drop down list in 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/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15077&viewfull=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/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15083&viewfull=1#post15083
Example: create drop down lists using VBA

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/showthread.php/2624-Drop-Down-Menu-with-Multiple-Conditions/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/showthread.php/2624-Drop-Down-Menu-with-Multiple-Conditions/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

[msiyab]

Hi Alan,

Thank you for your assistance.

I had an idea on how to create drop downs 1 & 2. The only reason i did not mention about it in my post was because i thought dependent lists worked differently. So I thought it was better to get help from drop down 1 itself.

Drop down 3 & 4 are very tricky and is out of my league and understanding. Hopefully you can guide me through with it.

[DocAElstein]

Hello Siyab,

...... i thought dependent lists worked differently. So I thought it was better to get help from drop down 1 itself.
Drop down 3 & 4 are very tricky and is out of my league and understanding. Hopefully you can guide me through with it.I expect your thinking is not necessarily incorrect: I would expect that there is an efficient way to look at this problem considering all 4 list together.
But I don’t know how to do it that way. I only have limited basic VBA knowledge.

So my strategy is to treat all 4 lists as independent lists created in the same way using VBA
List 1 and 2 can be either made once, or assumed to have been made.
So we will leave them made for now. Or if you start with Worksheet Appraisals having no drop down lists, then you would use this macro to make just lists 1 and 2: https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15073&viewfull=1#post15073


The solution I am proposing is not difficult, and is possibly not the most efficient.

Drop down List 3 and 4 will be remade / recreated by VBA coding , every time a different selection is made from drop down list 1 or 2.
When a different selection is made from list 1 or 2 , then that results in the cell to which they are associated changing its value. As far as VBA is concerned , it sees a cell change. ( VBA does not recognise you using the drop down list itself, but it does detect the cell value change).

We can use “Event coding” to detect a cell change. This can be used to trigger the re creating of the Lists 3 and 4.

We need to access the worksheet code module for worksheet Appraisals , and one of the available event codings, which is always there, but empty with no coding in it.
https://imgur.com/rteyeHM https://i.imgur.com/rteyeHM.jpg -- select the worksheet code module
https://imgur.com/ex9FlRI https://i.imgur.com/ex9FlRI.jpg -- select worksheets event codings
https://imgur.com/ZjEw5xy https://i.imgur.com/ZjEw5xy.jpg - - - we want the Worksheet_Change( ) macro


We must add coding to it, ( put coding inside it ) , for example, I have made a start for you here: https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15087&viewfull=1#post15087



Summary of coding: ( Private Sub Worksheet_Change(ByVal Target As Range) -----
https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15087&viewfull=1#post15087 )
Rem 1
We usually restrict most of the macro workings to only be done when specific ranges are chosen. In our case those ranges are column A and C from row 2 to row 8

Rem 2
In this section we go through the combinations of SOCIAL COMPETENCIES and Please Choose, and then create the appropriate drop down lists 3 and 4

‘2a) Communicating effectively
_... ‘2a(i) Does Not Meet Expectation
_... ‘2a(ii) Meets Expectation
_... ‘2a(iii) Exceeds Expectation

‘2b) Resolving Conflict
_... ‘2b(i) Does Not Meet Expectation
_... ‘2b(ii) Meets Expectation
_... ‘2b(iii) Exceeds Expectation

‘2c) Sharing Information
_... ‘2c(i) Does Not Meet Expectation
_... ‘2c(ii) Meets Expectation
_... ‘2c(iii) Exceeds Expectation

‘2d) Supporting Co-workers
_... ‘2d(i) Does Not Meet Expectation
_... ‘2d(ii) Meets Expectation
_... ‘2d(iii) Exceeds Expectation



So I made a start for you.
If you study the coding and get some understanding of it, then I think you can complete the macro

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



Alan

[msiyab]

So I made a start for you.
If you study the coding and get some understanding of it, then I think you can complete the macro

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

Alan

Hi Alan,

I tried to complete the code under Rem 2, but something has gone wrong. the drop downs do not work. Sorry my VBA knowledge is close to Zero. Please have a look at it whenever its possible.

3458

[DocAElstein]

Hello Siyab,
Your attempt ( https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15091&viewfull=1#post15091 ) , looks to me, at first glance, to be ( almost *** ) perfectly correct!.
I have not checked every detail, but it appears, at first glance , as if you have done everything exactly as I was suggesting.

Furthermore , the macro appears to work when I try it.

So I am not sure what the problem is. I can see no problem.

Your macro version is working as I expect. Your macro version appears to me to work perfectly:
If I make any changes in a row in ranges A2:A8 or C2:C8 in worksheet Appraisals, then the macro makes the drop down lists in that same row in columns D and E in worksheets Appraisals. Any newly made drop down lists, appear to work normally.



Possibly, I have misunderstood what you want.

If you continue to have problems then :-
Explain carefully, an example that you try that does not work.

Tell me exactly what you do, and what results you are expecting. Tell me what does or does not happen







almost ***
***[size=1] ( There were just some very minor typos in the comment notation '2a

[msiyab]

Hi,

Sorry, i managed to get the file to work after saving it as "Macro-Enabled". Silly me.

But now the trouble is bigger. What i thought would have been a simple macro or some Data Validation tweaks has turned out be a nightmare (for me).

I initially shared just one Topic/Header to find out the solution to my drop down list. But in actual i have 4 Topics which have more selections under each topic. I thought I could easily replicate the solution provided to the other topics.


I have attached the file, with the actual template of my file.

3460

As you can see, Cell A25, A28, A31 & A34 are the Topics, under which the drop down items change. I feel it would be a real tedious task to integrate the macro in the attached format. I have done the write-up for all topics in Comments & Advise sheets, as per the previous file.

Will it be too complicated?

[DocAElstein]

Hi Siyab
:confused: - Sorry, I don’t understand anything in your last post (https://excelfox.com/forum/showthread.php/2676-Dependent-Drop-Down-Lists?p=15104&viewfull=1#post15104)

I have no idea what it is that you are talking about.

Try again to explain carefully, what it is that you are trying to do, or wanting to do.
Take your time to explain carefully , possibly with an example.
Walk me carefully through an example , tell me exactly what you do and what you want to happen as a result of that.

I can’t respond or reply quickly, so take your time, and try not to miss out any details.

Alan

[msiyab]

Alright, let me try to explain everything from the beginning.

The sheet named "Actual Appraisal Form" is where I want the drop down to be placed (Row 25 to 36 to be precise).

Cell A25, A28, A31 & A34 are the categories under which each employee will be evaluated. Lets call them as "Topics" from now on. They are highlighted in Grey.


Under each Topic , there are sub points, For example, in the sheet "Comments", Column D shows the Topics and Column E shows the sub points.

1) Now what is required is that I need a drop down menu under Cell A25 (Social Competencies) which shows the sub points in Column E of the "Comments" sheet.

2) 2nd drop down will be under cell C26. Which will contain 3 common items (i.e., 'Does Not Meet Expectations', 'Meets Expectations', 'Exceeds Expectations')

3) Based on the previous 2 cells' (A26 & C26) selection, 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 C "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". In the Comments sheet, you can see subtopics highlighted in peach color, and right next to it is the Topic mentioned just for your reference.

4) So, If a person selects "Sharing Information" subtopic 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". In the Advise sheet, you can see subtopics highlighted in peach color, and right next to it is the Topic mentioned just for your reference.

Please do let me know if my explanation was clear this time.

[DocAElstein]

OK, I think I understand now.
In your last post, post #9 (https://excelfox.com/forum/showthread.php/2676-Dependent-Drop-Down-Lists?p=15106&viewfull=1#post15106), you gave basically the same explanation as in your very first post, post #1 (https://excelfox.com/forum/showthread.php/2676-Dependent-Drop-Down-Lists?p=15071&viewfull=1#post15071),
The small difference is that instead of a range A1:E8 we now have a range A25:G27
So you already have the coding for that, you just need to change the ranges appropriately

The coding would take this sort of form….

_____ If Application.Intersect(Target, Me.Range("A26:A27,C26:C27")) Is Nothing Then
‘ code like before with ranges adjusted appropriately


Now I see we basically have 3 extra ranges. In other words, we are really just doing the same thing again another 3 times





Will it be too complicated?
I don’t think it is particularly complicated. In basic terms you are doing the same thing again 4 times instead of 1 time.
There is no real new complications:
So you did it once, and can do it again ( and again and again and again !!! )


... I feel it would be a real tedious task... I suppose writing coding is always a bit tedious.
It is not too bad in your particular requirement because we have a lot of very similar coding. We can do a lot of copying ( Ctrl+c ) and pasting ( Ctrl+v ) . After that you need to make minor changes to ranges.



There are two basic ways that I see to do this

Way 1

The macro would look basically like this , and be approximately 4 times as long as previously:
_____ If Application.Intersect(Target, Me.Range("A26:A27,C26:C27")) Is Nothing Then
‘ code like before with ranges adjusted appropriately

_____ If Application.Intersect(Target, Me.Range("A29:A30,C29:C30")) Is Nothing Then
‘ code like before with ranges adjusted appropriately

_____ If Application.Intersect(Target, Me.Range("A32:A33,C32:C33")) Is Nothing Then
‘ code like before with ranges adjusted appropriately

_____ If Application.Intersect(Target, Me.Range("A35:A36,C35:C36")) Is Nothing Then
‘ code like before with ranges adjusted appropriately


Way 2

This might require you to slightly reorganise your Advise data so that different sections are at similar offsets to each other

We would then have a macro very similar to the previous starting something like this

_____ If Application.Intersect(Target, Me.Range("A26:A27,C26:C27, A29:A30,C29:C30, A32:A33,C32:C33, A35:A36,C35:C36")) Is Nothing Then
‘ code like before with ranges determined by an offset. This offset will vary according to which of the 4 main ranges were selected

Way 2 would be a shorter code, but would take a bit more brain work to figure out the logic


I expect if I was in your position, I would do it in Way 1. There are no real advantages of Way 2
There are no problems with having a macro 4 times as long as the previous one, and you don’t need to type it all,
since you can get most of it by copying ( Ctrl+c ) and pasting ( Ctrl+v ) what you already have.




I am not sure how I can help further?

Tell me if you want me to make a start for you as I did before.


Alan

[msiyab]

I am not sure how I can help further?

Tell me if you want me to make a start for you as I did before.


Alan

Yes please. Could you help me by starting it off for me like you had done previously. I will try the copy pasting and minor adjustments from where you leave it. Once its done on the file I have shared, i will try it on my original file.

I'd really appreciate the effort.

[DocAElstein]

Hi
I will do the easy first part now, and when I have time, possibly tomorrow , I will do the more complicated second part*

Once again we have two parts to your requirement

Part one, is fairly simple: As before, the Lists for columns A and C need to made
We just have 2x4 = 8 list ranges to make normally. As previously we can do that manually or with coding.




First two list sets ( column A and C ranges ), with coding
I have done a couple of macros for you:

Sub MakeNormalDropDowns2x4()
This macro is basically the same basic idea as the macros for Drop down Lists 1 and 2 which I have already done for you.
But now instead of creating the lists over two ranges, ( previously A2:A8 and C2:C8 in Appraisals worksheet ) , we now are doing it for the following ranges in worksheet Actual Appraisal Form
Rem 2 SOCIAL COMPETENCIES
'2a) Topic SOCIAL COMPETENCIES List 1 in column A
A26:A27
'2b) Please Choose List 2 in column C
C26:C27

Rem 3 PERSONAL COMPETENCIES
'3a) Topic PERSONAL COMPETENCIES List 1 in column A
A29:A30
'3b) Please Choose List 2 in column C
C29:C30

Rem 4 METHODOLOGICAL COMPETENCIES
'4a) Topic METHODOLOGICAL COMPETENCIES List 1 in column A
A32:A33
'4b) Please Choose List 2 in column C
C32:C33

Rem 5 LEADERSHIP COMPETENCIES
'5a) Topic LEADERSHIP COMPETENCIES List 1 in column A
A35:A36
'5b) Please Choose List 2 in column C
C35:C36

_._____________________

I have done a second alternative for you. This is just to demonstrate how we can reduce some coding sometimes using a combination of looping and the Range Offset Property (https://docs.microsoft.com/en-us/office/vba/api/excel.range.offset)
Sub MakeNormalDropDowns1x4andLoop4times()

Both those macros are here:
https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15111&viewfull=1#post15111

Share ‘Appraisal - Drop Down 11 11.xls’ : https://app.box.com/s/wj11tpgc9fsuoekp023cd7ndkkqyvtm1

It should be noted, that in a situation like this there is little advantages of shortening the macro. It comes down to personal choice, mostly. The performance of the two macros will be about the same.
The shorter macro looks tidier, and possibly looks a bit more clever or professional, but is likely to be less easy to modify or understand at a later date. I personally would usually use the first macro. The final file might be a bit bigger for the longer macro, but with modern computers the extra space used rarely is of great significance.





I have not checked thoroughly to see if I made any simple mistakes. I leave that to you to test thoroughly.


In my next post I will tackle the second part of your current requirement.
I will do that for you tomorrow*, or when I have more time. ….





One last thing today, – this is just out of passing interests.
This is not part of your requirement, but I did it out of my own interest a few days ago.
You might be interested to take a quick look here:
https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15092&viewfull=1#post15092

It is possible to make the drop down lists appear when the cell is selected
I applied that to your initial requirement, just for fun…


Alan










Share ‘Appraisal - Drop Down 11 11 First Part.xls’ : https://app.box.com/s/wj11tpgc9fsuoekp023cd7ndkkqyvtm1

[DocAElstein]

Hi
Here is the next macro for you:
Share ‘Code Appraisal - Drop Down 11 11 xls .txt’ : https://app.box.com/s/jd6mgsnd5mkwuidi2idrpf72a3d91xvq
Share ‘Appraisal - Drop Down 11 11.xls’ : https://app.box.com/s/vuggryhlalxu3qjeztkt2jby3wv9jzoj
https://pastebin.com/Avgsv1h6
https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15118&viewfull=1#post15118

I expect I may have made some small mistakes. I leave it to you to check thoroughly.

As you suggested, it is tedious work. But if you approach it carefully, it is not difficult.
Here some of the notes I made as I went along
https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15114&viewfull=1#post15114
https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15115&viewfull=1#post15115
https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15116&viewfull=1#post15116
https://excelfox.com/forum/showthread.php/2561-Appendix-Thread-(-Codes-for-other-Threads-etc-)-Event-Coding-Drpdown-Data-validation?p=15117&viewfull=1#post15117








Some extra suggestions
I showed you yesterday, ( Sub MakeNormalDropDowns1x4andLoop4times() ) how we can approach things slightly differently using looping and offsets.
There can be a couple of advantages to this:
_ 1. It looks a bit simpler and tidier ( it may not finally work any better )
_ 2. In some cases it might make further additions to data easier…

In order for this to work, it would be very helpful if you could have a regular structure in your data
For example, your Comment worksheet should be structured so:
Share ‘Some extra suggestions.xls’ : https://app.box.com/s/ux77egbft9kipdtiht21017y4iixp854

Similarly you should structure your Advice worksheet in a similar, regular, way.

Such regular structures would allow the easier implementation of the looping and Range.Offset coding ideas

That is just something for you to consider in the future



( When you have your final solution it would be courteous and helpful to others if you could share your solution )






Alan