Conditional / Drop Down List Multiple Filter

[sourabh_ajmera]

Hi everyone,

Background:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]Policy[/TD]
[TD]Issue[/TD]
[TD]Service[/TD]
[TD]Benefits[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Application[/TD]
[TD]Service Request[/TD]
[TD]Analysis[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]underwriting[/TD]
[TD]Policy Accounts[/TD]
[TD]Payout[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Payment[/TD]
[TD]Admin[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 753"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The Blue text in Column A is level 1 data; the red text in B,C and D column is Level 2 data and; the black text in column B,C and D are level 3 data.

Questions/Problem:
I am trying to filter data in column A and based on that selection I would like to see level 2 data and then if I choose one of the level 2 data I need to see accordingly level 3 data.

For e.g:
Once I choose Policy, I need to see 3 options Issue, Service and Benefits. Now I can choose any of these 3 options, lets say Benefits, so now I need to see Analysis, Payout and Admin.

How would I go about this?

I have tried using Pivot/Slicer - Its a temp solution but need to get some solid solution.

Really appreciate your help in this!

 

[MickG]

Try this Code for Validation in "G1". (Change "G1" in code to suit your requirements)
Paste code to Data sheet module.( Place at top of code page)
To Run code Double Click "G1", the Text:- "Policy" should show, Select from Drop down list for Related column Data.
To Select another column Header, Double click "G1", and start again.

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR="Navy"]As[/COLOR] Range, Cancel [COLOR="Navy"]As[/COLOR] Boolean)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "G1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = ActiveSheet.Cells(1).CurrentRegion
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] Ac = 2 To Rng.Columns.Count
            [COLOR="Navy"]For[/COLOR] n = 2 To Rng.Rows.Count
                
                [COLOR="Navy"]If[/COLOR] Not Dic.exists(Rng(1, Ac).Value) [COLOR="Navy"]Then[/COLOR]
                    Dic.Add (Rng(1, Ac).Value), Rng(n, Ac)
                [COLOR="Navy"]Else[/COLOR]
                    Dic(Rng(1, Ac).Value) = Dic(Rng(1, Ac).Value) & "," & Rng(n, Ac)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]Next[/COLOR] Ac
nstr = Join(Application.Transpose(Application.Transpose(Rng(1).Offset(, 1).Resize(, Rng.Columns.Count - 1))), ",")
Target = "Policy"
[COLOR="Navy"]With[/COLOR] Target.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=nstr
    .ShowError = False
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "G1" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dic.exists(Target.Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]With[/COLOR] Range("G1").Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:=Dic(Target.Value)
                .ShowError = False
            [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick