Multi-Level Data Validation (Again...i know....)

M

mm2011

New Member
Joined
Feb 3, 2011
Messages
5
First, let me say that I have read the previous posts about using the INDIRECT() function in conjunction with the data validation function in excel, however, this does not achieve what I am looking to do.

I have a "database" worksheet whose first two columns (A:B) are categories and types, respectively. Now, what I am looking to do is to have two separate dropdown menus which show only unique values in each column and filters the second dropdown based on the selection in the first. For example, say the categories are Fruits and Veg and the Types are apples and kale. If fruit is selected, I only want to show apples, not kale.

The INDIRECT() method will not work because I do not have a separate list of the categories and types. This is because in practice this is a dynamic workbook and changes only happen on the database sheet.

Hopefully that makes sense!

Thank so much for your help! You guys really do rock.

mm
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi MM,

I have worked it out but couldn't get it to work without creating a help sheet.
Hope you're familiar with named ranges because that's the trick behind it.

1. create a named range on sheet1 column A with this formula =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000)). [in my example the range is named Country_all]
2. create the help sheet
3. in cell a2 enter this array formula =IFERROR(INDEX(country_all,MATCH(0,COUNTIF($A$1:A1,country_all),0)),"") [if you enter the formula correctly Excell will place the formula between curly braces {}]
4. copy the formula down to the extend you need, this will create the distinct list of countries.
5. create a named range of the distinct list of countries on sheet 2 and call it country_lov
6. create a data validation cell and put country_lov in as input for the list.

create the 2nd dependable data validation:
1. create a named range on sheet1 column B with this formula =OFFSET(Sheet1!$b$2,0,0,COUNTA(Sheet1!$b$2:$A$1000)). [in my example the range is named product_all]
2. go to the help sheet
3. in cell b2 enter this array formula =IFERROR(INDEX(product_all, MATCH(0, COUNTIF($B$1:B1, product_all)+(country_all<>help!$F$2), 0)),"") [if you enter the formula correctly Excell will place the formula between curly braces {}]
4. copy the formula down to the extend you need, this will create the distinct list of countries.
5. create a named range of the distinct list of products on sheet 2 and call it product_lov
6. create a data validation cell and put product_lov in as input for the list.

Be aware:
1. all formula's on the help sheet are array formula's, so enter them using CTRL-SHFT-ENTER.
2. the unique list of values from column B is dependable on the input from the first data validation and if that cell (in my case sheet2!F2) is blank the formula will return blanks. That's no error, as soon as you have used the first data validation dropdown it will fill itself.

Hope this will help.

Rgs,
 
Upvote 0
I believe this will do what you want, if I understand correctly.

Copy change event and function to your sheet module.

With a list like this (unsorted is fine) in A and B, add a Data Validation drop down to cell D1, > List > Fruits,Nuts,Veg >OK.


Fruit apple
Nuts Brazil
Veg corn
Fruit pear
Nuts Peanuts
Veg carrots
Fruit peach
Nuts Pecans
Veg kale
Fruit plum
Nuts Filberts
Veg tomato
Fruit cherry
Nuts Almonds
Veg potato
Fruit prune
Nuts Pistachios
Veg lettuce
Fruit banana
Nuts Walnuts
Veg beets

Now see drop down in E1 display the choices selected in D1. (The second drop down is created by the code)

Howard



Code: 
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
'/// by: Siddharth Rout

    Dim i As Long, LastRow As Long, n As Long
    Dim MyCol As Collection
    Dim SearchString As String, TempList As String
 
    Application.EnableEvents = False
 
    On Error GoTo Whoa
 
    '~~> Find LastRow in Col A
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Set MyCol = New Collection
 
        '~~> Get the data from Col A into a collection
        For i = 1 To LastRow
            If Len(Trim(Range("A" & i).Value)) <> 0 Then
                On Error Resume Next
                MyCol.Add CStr(Range("A" & i).Value), CStr(Range("A" & i).Value)
                On Error GoTo 0
            End If
        Next i
 
        '~~> Create a list for the DV List
        For n = 1 To MyCol.Count
            TempList = TempList & "," & MyCol(n)
        Next
 
        TempList = Mid(TempList, 2)
 
        Range("D1").ClearContents: Range("D1").Validation.Delete
 
        '~~> Create the DV List
        If Len(Trim(TempList)) <> 0 Then
            With Range("D1").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                 xlBetween, Formula1:=TempList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    '~~> Capturing change in cell D1
    ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
        SearchString = Range("D1").Value
 
        TempList = FindRange(Range("A1:A" & LastRow), SearchString)
 
        Range("E1").ClearContents: Range("E1").Validation.Delete
        [E1].Activate
        If Len(Trim(TempList)) <> 0 Then
            '~~> Create the DV List
            With Range("E1").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                 xlBetween, Formula1:=TempList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        End If
    End If
 
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub
 
'~~> Function required to find the list from Col B
Function FindRange(FirstRange As Range, StrSearch As String) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Dim strTemp As String
 
    Set aCell = FirstRange.Find(what:=StrSearch, LookIn:=xlValues, _
    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
 
    ExitLoop = False
 
    If Not aCell Is Nothing Then
        Set bCell = aCell
        strTemp = strTemp & "," & aCell.Offset(, 1).Value
        Do While ExitLoop = False
            Set aCell = FirstRange.FindNext(After:=aCell)
 
            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                strTemp = strTemp & "," & aCell.Offset(, 1).Value
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = Mid(strTemp, 2)
    End If
End Function
 
Upvote 0
To add onto what Howard has suggested here... if you have a list in column A that is ever-growing and changing, and you don't want to have to manually manage the first drop down, you can have this dynamically expand by creating a helper list (column I in my example below) to drive the first dropdown. Everything else will be as Howard explained, your data validation would just reference a named range "DV_Cat1" (remove quotes) as the source.

Excel 2010
ABCDEFGHIJKLM
FruitappleDefined Ranges
NutsBrazilFruitName: DV_Cat1
VegcornHumanRefers to:=Sheet6!$I$2:INDEX(Sheet6!$I$2:$I$1000,SUMPRODUCT(--(Sheet6!$I$2:$I$1000<>"")))
FruitpearNuts
NutsPeanutsVegName:Cat1
VegcarrotsRefers to:=Sheet6!$A$1:$A$35
Fruitpeach
NutsPecans
Vegkale
Fruitplum
NutsFilberts
Vegtomato
Fruitcherry
NutsAlmonds
Vegpotato
Fruitprune
NutsPistachios
Veglettuce
Fruitbanana
NutsWalnuts
Vegbeets
NutsBarry
HumanMichael

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFC000"]Human[/TD]
[TD="bgcolor: #FFC000"]Michael[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Auto Expanding DV_Cat1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Cat_1, MATCH(MIN(IF(ISBLANK(Cat_1)+COUNTIF(I1:$I$1, Cat_1), "", IF(ISNUMBER(Cat_1), COUNTIF(Cat_1, "<"&Cat_1), COUNTIF(Cat_1, "<"&Cat_1)+SUM(IF(ISNUMBER(Cat_1), 1, 0))+1))), IF(ISBLANK(Cat_1)+COUNTIF(I1:$I$1, Cat_1), "", IF(ISNUMBER(Cat_1), COUNTIF(Cat_1, "<"&Cat_1), COUNTIF(Cat_1, "<"&Cat_1)+SUM(IF(ISNUMBER(Cat_1), 1, 0))+1)), 0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Cat_1[/TH]
[TD="align: left"]=Sheet6!$A$1:$A$35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Data Validation question
Replies
4
Views
251
Peter_SSs
Peter_SSs
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
380
RoryA
RoryA
R
  • Question Question
I need to return a lookup on a table once a slicer has been selected.
Replies
3
Views
472
JamesCanale
JamesCanale
trishcollins
  • Question Question
VBA to return the offset formula used in the data validation source
Replies
1
Views
376
Jeffrey Mahoney
Jeffrey Mahoney
W
  • Question Question
Use data from cells of one worksheet table to determine table & column selection in a different worksheet.
2
Replies
10
Views
584
Worksong
W

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top