How can I create dependent & dynamic DropDown lists that start with a certain string?

[mcclausky]

Hello,

I would like to create 3 levels of dependent & dynamic DropDownlists (Data Validation lists) based on the following ranges:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]LEVEL 1[/TD]
[TD="align: center"]LEVEL 2[/TD]
[TD="align: center"]LEVEL 3[/TD]
[/TR]
[TR]
[TD]1. ACTIVE[/TD]
[TD]1.1. ACTIVE CURRENT[/TD]
[TD]1.1.1. AVAILABLE[/TD]
[/TR]
[TR]
[TD]2. PASSIVE[/TD]
[TD]1.2. ACTIVE NON CURRENT[/TD]
[TD]1.1.2. ACTIVE EXIGIBLE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2.1. PASSIVE CURRENT[/TD]
[TD]1.2.1. ACTIVE FIXED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2.2. PASSIVE NON CURRENT[/TD]
[TD]1.2.2. CUMULATED DEP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2.1.1. PASSIVE EXIGIBLE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2.1.2. PASSIVE EXIGIBLE LT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2.2.1. PASSIVE EX.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2.2.2. PASSIVE EX. LT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The 3 ranges are created as: Level_1, Level_2, Level_3.

• DropDown list 1 must display items from range Level_1.
• DropDown list 2 must display FILTERED items from range Level_2. Example: if "1.ACTIVE" is selected on DropDownList 1 then the available items on DropDown 2 should only be: "1.1.ACTIVE CURRENT" and "1.2.ACTIVE NON CURRENT". The formula for the Data validation on DropDown 2 should filter the Level_2 range by the first 2 chars selected on DropDown1, in this case it should return a list of items that start with "1.", which are items 1.1. and 1.2.
• DropDown list 3 must display FILTERED items from range Level_3. Example: if "2.1. PASSIVE CURRENT" is selected on DropDownList 2 then the available items on DropDown 3 should only be: "2.1.1..." and "2.1.2. ...". The formula for the Data validation on DropDown 3 should filter the Level_3 range by the first 4 chars selected on DropDown2, in this case it should return a list of items that start with "2.1.", which are items 2.1.1. and 2.1.2.

I have tried with several OFFSET formulas and some worked, but unfortunately the OFFSET formula on the DropDowns stops working when you close and re-open the excel file. That's why I'm asking for your help, perhaps there is another way to achieve this?

Your help will be greatly appreciated.

Mc

 

[Derek Brown]

Perhaps the following will help:
https://www.youtube.com/watch?v=zKzPWz0Fg6U

 

[MickG]

This code assumes that your data list is in sheet2 (A to C) and your validation list will be in sheet 1 "A1 to C1".
Paste this code in sheet1 code module.
To run the code you need to first activate sheet1 (click another sheet tab then back to sheet1.
This will fill validation in "A".
Select value in "A" for validation in "B" , and "B for C".

Private Sub Worksheet_Activate()
Dim Rng As Range, Dn As Range
With Sheets("Sheet2")
 Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
With Range("A1").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="='" & Sheets("Sheet2").Name & "'!" & Rng.Address
End With
Range("A1").Offset(, 1).Resize(, 2) = ""
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range, Str As String, nStr As String
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:B1")) Is Nothing And Target.Count = 1 Then
With Sheets("Sheet2")
Select Case Target.Address(0, 0)
    Case "A1": Set Rng = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
    Case "B1": Set Rng = .Range(.Range("C2"), .Range("C" & Rows.Count).End(xlUp))
End Select
End With
For Each Dn In Rng
Str = Split(Target, " ")(0)
If Left(Split(Dn.Value, " ")(0), Len(Str)) = Str Then
    nStr = nStr & IIf(nStr = "", Dn.Value, ", " & Dn.Value)
End If
Next Dn
With Target.Offset(, 1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=nStr
End With
Target.Offset(, 1).Resize(, 2) = ""
End If
Application.EnableEvents = True
End Sub

 

[Mike7952]

See solution at How can I create dependent & dynamic DropDown lists that start with a certain string?

 

[mcclausky]

See solution at How can I create dependent & dynamic DropDown lists that start with a certain string?

Mike, you are great!

Your solution worked perfectly. My previous solution was somehow similar but a bug in excel prevented it to work after re opening the file. It was something like this.
=OFFSET(INDEX( level_2_items, 1), MATCH(LEFT(F2,2), LEFT(level_2_items, 2), 0)-1, 0, SUMPRODUCT(--(LEFT(level_2_items,2)=LEFT(F2, 2) )),1)

Anyway, thank you so so much!!!