Cascading drop down list

[antros48]

Hi all. I know that this thread is multiple times posted but i wasn't able to take what i wanted.
I have an excel sheet that contains horizontally the multiple departments of a company that splits on the various subdivisions and later on on the various role positions. The list is horizontal and what i am trying to achieve is a cascade drop down list so that the user first selects the department, and on the next cell the list to be updated with only the subdivisions available on that department and finally based on that selection to be able to select on the next drop down list the roles specific on that department.
I tried a lot of online tutorials even with macros but couldn't get what i wanted. Any help appreciated.

[AliGW]

Which online tutorials did you try and in what way did they not work for you? This is all a bit vague.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[antros48]

Firstly thank you for the response and the information provided.
Basically, i tried defining lists on name manager but there are a lot
of possible combinations to be able to define so many names. Also
i tried extracting the filtered results of the second column
based on the first column choice and then input them as data
on data validation for the second drop list, again without any
success.
In the dummy attached i replaced all data with fake for obvious reasons.
The first selection is the name of the country, which will eliminate
the results to only the specific to that choice for the next
selection and goes on.
If there still are more questions for clarifications please let
me know.

[Glenn Kennedy]

1. Is it possible to rearrange the data to have 1 column for America, one for Europe, etc?

2. is your final choice binary and the same for each country (male/female or Y/N) or have you oversimplified your example?

3. In total, how many levels are there?

[antros48]

Unfortunately this is the given structure, because the next step is to extract some information which are provided on each case chosen and which are written on the same row.
The choices are not binary and as you correctly guessed i did an oversimplification.
There are 4 levels and the choices on each level are a lot..
I actually found this https://www.extendoffice.com/documen...ist-boxes.html link which helped me a little but it is kind of buggy on the results.

[kvsrinivasamurthy]

Work sheet event can be used. or helper columns are to be used.
Which one you prefer.

[Glenn Kennedy]

I can't/won't do anything with an incorrect sample. First step is to show us EXACTLY how your data are structured, in a REPRESENTATIVE sample sheet.

[antros48]

Ok i will try to elaborate even more on the overall task i have to do. Updated excel is attached.
I have supposedly two group of data. The first group indicates the current position of a person and these data have to be selected from the user using lets say a drop down list. There are four levels of selection but the last level is unique for each case so there is nothing to choose from. This is the reason i didn't include it in the original excel. The choices are NOT binary and there are plenty of choices but given a bad example i chose the gender case.
Each choice that the user finally makes, results in 3 characteristics. These has to be printed somewhere, for example next to the Own characteristics cell i provided.
The next task is to try and find the possible destinations located on the second table and the criteria for that is to have a 2/3 match on the data provided for each case on the second table. These possible destinations are to be printed and provided to the user as outcome.
This is the task i try to do.
Regarding work sheet events or helper columns i really have no preference as long as this is viable..
Again thank you for assistance.

[Glenn Kennedy]

I'm more confused than previously!! What do you expect to see and where do you expect to see it?

I thought I had a bright idea about how to do this... but it didn't work out.... So, one further question, to see if Plan B is viable. Over how many rows do you want to have this series of drop-down boxes operating?

[antros48]

Ok never mind about countries and genders :p I have four level cascading drop down list that each choice has to contain filtered data based on the previous choice. The data are in horizontal structure like i provided. Each final choice after completing the four choices has a corresponding 3 data cells.
These cells must be compared with the corresponding three data cells on another table and where i have a match (e.g. where at least two out of three data) the choices of the second table will be extracted e.g. as shown next to the Possible Destinations part.
I expect to see the percentage match next to the second table as shown on the example and next to the possible destinations to see the cases where there is a match greater than 2/3.
The rows for the drop down list are in total 57.

[Glenn Kennedy]

There are significant issues with this. Plan B has died... Please confirm that you are still using Excel 2007... Excel 2010+ would have been better!

[antros48]

Yes. I am using definitely Office 2013 and above.

[AliGW]

Please update your forum profile to reflect this - it is currently showing Excel 2007.

[antros48]

Apologize. Just changed it.

[AliGW]

Thanks for that.

[Glenn Kennedy]

OK. I've just seen your reply. I've done this using Excel 2007-friendly formulae. I'm still not sure if I understand what you want. But take a look at this and see if it's doing what you wanted regarding 3 level dropdowns.

I had to sort your data alphabetically by column B, then by C, then by D... otherwise it would have become horribly messy, I think. I won't explain anything yet, as I may be miles out in my guess!!!

[kvsrinivasamurthy]

Here is the code for Worksheet event.
Range for Validation list is B3:D25. Range for selection of lists is G4:I20.
They can be changed individually in the code if required.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Target, Range("G4:G20")) Is Nothing Then
Target.Offset(0, 1) = ""
End If
If Not Intersect(Target, Range("H4:H20")) Is Nothing Then
Target.Offset(0, 1) = ""
End If

Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo line1

'First Level**********

If Not Intersect(Target, Range("G4:G20")) Is Nothing Then

Application.EnableEvents = False
Dim FstRng As Range, cel As Range
Dim FstStr As String, FstVal As String

Set FstRng = Range("B3:B25")
    
For Each cel In FstRng
    If InStr(1, FstStr, "," & cel & ",") = 0 Then
    FstStr = FstStr & "," & cel & ","
    FstVal = FstVal & "," & cel
    End If
Next cel
If FstVal <> "" Then FstVal = Mid(FstVal, 2)

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=FstVal
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
'Application.EnableEvents = True
End If

'Second Level***************************

If Not Intersect(Target, Range("H4:H20")) Is Nothing Then

Application.EnableEvents = False
Dim SecRng As Range, cel1 As Range
Dim SecStr As String, SecVal As String

Set SecRng = Range("C3:C25")
    
For Each cel1 In SecRng
    If InStr(1, SecStr, "," & cel1 & ",") = 0 And cel1.Offset(0, -1) = Target.Offset(0, -1) Then
    SecStr = SecStr & "," & cel1 & ","
    SecVal = SecVal & "," & cel1
    End If
Next cel1
If SecVal <> "" Then SecVal = Mid(SecVal, 2)

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=SecVal
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

'Target.Offset(0, 1) = ""

Application.EnableEvents = True
End If

'Third Level*********

If Not Intersect(Target, Range("I4:I20")) Is Nothing Then

Application.EnableEvents = False
Dim ThiRng As Range, Cel2 As Range
Dim ThiStr As String, ThiVal As String

Set ThiRng = Range("D3:D25")
    
For Each Cel2 In ThiRng
    If InStr(1, ThiStr, "," & Cel2 & ",") = 0 And Cel2.Offset(0, -1) = Target.Offset(0, -1) _
                 And Cel2.Offset(0, -2) = Target.Offset(0, -2) Then
    ThiStr = ThiStr & "," & Cel2 & ","
    ThiVal = ThiVal & "," & Cel2
    End If
Next Cel2
If ThiVal <> "" Then ThiVal = Mid(ThiVal, 2)

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=ThiVal
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Application.EnableEvents = True


End If

line1:
Application.EnableEvents = True

End Sub

To paste the code
Right click on Sheet tab --> view code
VB window opens.
Paste the code
Close the window.

[ChemistB]

Like Glen, I had to use helper columns (B31:E36) and the data needs to be custom sorted, first by B, then C, then D, then E.

I used Name Manager to create names for list placements List, List1A, List2A, and List3A refer to the positions within your original data and List1,List2, List3 and List4 refer to the new lists in B31:E36 for the data validation dropdown cells. The dropdowns are in B29:E29

Example formulas for List2A

=INDEX(OFFSET(List1A,0,1),MATCH(Sheet1!$C$29,List1A,0)):INDEX(OFFSET(List1A,0,1),MATCH(Sheet1!$C$29,List1A))

For List 3

=Sheet1!$D$31:INDEX(Sheet1!$D$31:$D$36, SUMPRODUCT(--(LEN(Sheet1!$D$31:$D$36)>0)))

[Glenn Kennedy]

Chemist B... Confused. How can that work for 57 rows of dropdowns... or am I missing something?

[ChemistB]

I missed the part of having 57 rows of dropdowns. It would get very messy.

[Glenn Kennedy]

Hehehe! Nice to see someone of your calibre missing something!! I'm forever missing stuff...

[Lucko4Life]

I'm kind of confused on what you are looking for.
But I think I had a similar issue...You need the dependent drop down lists to be dependent on the list values that come before it. For example, the 3rd drop down list needs to be dependent on both the 2nd and 1st list values, and as a result, only the criteria that matches the values of list 1 and 2 will show up for list 3.

Is that similar to what you need? If so, I have attached something that might work for you.

*Edit: Also, I am sorry if this is not what you are looking for.

[Glenn Kennedy]

LuckofLife... I didn't do down that route for two reasons:

1. The data structure is a vertical table and (mainly),

2. This becomes a nightmare if there are a lot of options at each level (so many named ranges). With YOUR data structure, there is a way to do it with only 1 NR per DD column

[Lucko4Life]

Gleen Kennedy,
You are right. I am not satisfied with that solution either. I have been stuck on that for days and that is the best I could find so far, besides a script on Google Sheets (which is exactly what I want, but I need it for Excel unfortunately).
I have only been using Excel for about a month, so I have a lot to learn.

I did find this other workbook that someone posted on another forum years ago. They auto populate information from one data table onto helper columns and use that as the data source. But I do not know how to tailor it to this person's situation, or my own for that matter, or if that's even what they are asking for.

I'm just throwing stuff out there trying to help since others have helped me.

Here is that workbook I found, I am not sure if it will be of any help as I mentioned. I apologize if it's useless to you.

[antros48]

First of all thank you for the valuable response and the solutions provided. All of the solutions were exactly to the point and i think this thread will be valuable to others as well.
Glenn you got the point and thank you.
Even though the solutions work, i prefer working with macros instead of formulas. So i kept the kvsrinivasamurthy solution who i honestly thank.
I added the Target.Offset(0, 2) = "" on the first part of the code so that the rest of the choice cells to be cleared also when the user makes the choice on the first one.
The only bug i find is that when i replace the name Set SecRng = Range("C3:C25") with Set SecRng = Worksheets("Sheet2").Range("C3:C25") does not work and the outcome is kind of messed up. When i try this on the same sheet it works fine. Is the Intersect function buggy when the source data are in another worksheet?

[kvsrinivasamurthy]

Have you tried worksheet event given in post17.

[antros48]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Target, Range("B20:B20")) Is Nothing Then
Target.Offset(0, 1) = "Please Select"
Target.Offset(0, 2) = "Please Select"
Target.Offset(0, 3) = "Please Select"


End If
''If Not Intersect(Target, Range("H4:H20")) Is Nothing Then
''Target.Offset(0, 1) = ""
''End If
''If Not Intersect(Target, Range("I4:I20")) Is Nothing Then
''Target.Offset(0, 1) = ""
''End If
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo line1

'First Level**********

If Not Intersect(Target, Range("B20:B20")) Is Nothing Then

Application.EnableEvents = False
Dim FstRng As Range, cel As Range
Dim FstStr As String, FstVal As String

Set FstRng = Worksheets("Name of sheet").Range("A15:A309")
    
For Each cel In FstRng
    If InStr(1, FstStr, "," & cel & ",") = 0 Then
    FstStr = FstStr & "," & cel & ","
    FstVal = FstVal & "," & cel
    End If
Next cel
If FstVal <> "" Then FstVal = Mid(FstVal, 2)

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=FstVal
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
'Application.EnableEvents = True
End If

'Second Level***************************

If Not Intersect(Target, Range("C20:C20")) Is Nothing Then

Application.EnableEvents = False
Dim SecRng As Range, cel1 As Range
Dim SecStr As String, SecVal As String

Set SecRng = Worksheets("Name of sheet").Range("B15:B309")
    
For Each cel1 In SecRng
   If InStr(1, SecStr, "," & cel1 & ",") = 0 And cel1.Offset(0, -1) = Target.Offset(0, -1) Then
    SecStr = SecStr & "," & cel1 & ","
    SecVal = SecVal & "," & cel1
    End If
Next cel1
If SecVal <> "" Then SecVal = Mid(SecVal, 2)

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=SecVal
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Application.EnableEvents = True
End If

'Third Level*********

If Not Intersect(Target, Range("D20:D20")) Is Nothing Then

Application.EnableEvents = False
Dim ThiRng As Range, Cel2 As Range
Dim ThiStr As String, ThiVal As String

Set ThiRng = Worksheets("Name of sheet").Range("C15:C309")
    
For Each Cel2 In ThiRng
    If InStr(1, ThiStr, "," & Cel2 & ",") = 0 And Cel2.Offset(0, -1) = Target.Offset(0, -1) _
                 And Cel2.Offset(0, -2) = Target.Offset(0, -2) Then
    ThiStr = ThiStr & "," & Cel2 & ","
    ThiVal = ThiVal & "," & Cel2
    End If
Next Cel2
If ThiVal <> "" Then ThiVal = Mid(ThiVal, 2)

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=ThiVal
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Application.EnableEvents = True


End If

line1:
Application.EnableEvents = True

End Sub

This is your code but modified based on the actual needs. The results are that only the first choice cell has a drop down list and the others don't. Also the drop down list with the same data is created on other "random" cells of the same row.