How to create Dependent drop Down Lists in Excel

Hi all!

Im hoping someone can help a intermediate Excel user with the following:

Scenario #1: I have a database that's stored on a server.  I have an Estimate Wrokbook that requires the product information in that database.  Using the information in that database i want to create several drop down lists that are dependent on each other for the information presented in the next list.  For example, By choosing 'Purple' in the first drop down, the second drop down will on give me the results that pertain to purple.

Scenario #2:  Same as above, except that assuming the programming behind the above may be too complex for me, the database is located in the Estimate workbook.


Attached is a sample of what im looking for.  I have intermediate level Excel experience, with very little VBA so anything you give me should be tailored around that.  Any help with this would be appreciated!

Testing.xls
DadXSixAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
Take a look at this http:/A_5062.html and let me know if this is the direction you want to head and I'll assist.

I'm out for an hour or two but will be back to see any comments you might have and to assist from there.

Dave
0
krishnakrkcCommented:
Hi,

Try this. Go with scenario # 2

'Material Database' Sheet module


Private Sub Worksheet_Deactivate()
Dim nmMatType       As Name
Dim nmMatDesc       As Name
Dim nmMatTypeDesc   As Name
Dim nmUnqMatType    As Name
Dim LastRow         As Long
Dim MatType

On Error Resume Next
Set nmMatType = ThisWorkbook.Names("MatType")
Set nmMatDesc = ThisWorkbook.Names("MatDesc")
Set nmMatTypeDesc = ThisWorkbook.Names("MatTypeDesc")
Set nmUnqMatType = ThisWorkbook.Names("UnqMatType")
On Error GoTo 0

LastRow = Range("a" & Rows.Count).End(xlUp).Row

If nmMatType Is Nothing Then
    Range("b1:b" & LastRow).Name = "MatType"
ElseIf InStr(1, nmMatType.RefersTo, LastRow) = 0 Then
    Range("b1:b" & LastRow).Name = "MatType"
End If
If nmMatTypeDesc Is Nothing Then
    Range("a1:b" & LastRow).Name = "MatTypeDesc"
ElseIf InStr(1, nmMatTypeDesc.RefersTo, LastRow) = 0 Then
    Range("a1:b" & LastRow).Name = "MatTypeDesc"
End If
If nmMatDesc Is Nothing Then
    Range("a1:a" & LastRow).Name = "MatDesc"
ElseIf InStr(1, nmMatDesc.RefersTo, LastRow) = 0 Then
    Range("a1:a" & LastRow).Name = "MatDesc"
End If

MatType = UNIQUE(Range("MatType").Value)

With Range("az1")
    .Resize(UBound(MatType) + 1).Value = Application.Transpose(MatType)
    .Resize(UBound(MatType) + 1).Name = "UnqMatType"
End With
End Sub


'Estimating Template' sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strDesc As String
    Dim vList
    If Target.Row < 15 Then Exit Sub
    If Application.Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
    vList = Sheet1.Range("MatTypeDesc")
    vList = UNIQUE(vList, Target.Text, 2, 1)
    strDesc = Join$(vList, ",")
    With Target.Offset(, 1).Validation
        .Delete
        .Add xlValidateList, , , strDesc
    End With
       
End Sub

In a standard module

Function UNIQUE(ByRef d, Optional ByVal Crit, Optional ByVal CritCol, Optional ByVal RsltCol)
Dim i As Long

With CreateObject("scripting.dictionary")
    .comparemode = 1
    If IsMissing(Crit) Then
        For i = 2 To UBound(d, 1)
            If Len(d(i, 1)) Then
                .Item(d(i, 1)) = Empty
            End If
        Next
        If .Count Then UNIQUE = .keys
    Else
        For i = 1 To UBound(d, 1)
            If Len(d(i, RsltCol)) Then
                If LCase$(d(i, CritCol)) = LCase$(Crit) Then
                    .Item(d(i, RsltCol)) = Empty
                End If
            End If
        Next
        If .Count Then UNIQUE = .keys
    End If
End With
End Function

Kris
0
krishnakrkcCommented:

In Column B on Template sheet use named range "UnqMatType' for data validation

Kris
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

dlmilleCommented:
@DadXSix - your solution is here.

Given that I had just published a related article (re: http:/A_5062.html ) which enables the creation of sustained unique/distinct lists for Combo Boxes, and am in the progress of making this utility more "generic" for use with any Active-X List Object and even Data Validation lists, I thought this would be a great opportunity to leverage these techniques, here.

This implementation does the following:

1.  Dynamically generates the unique drop-down list for the Material Type column,
2.  Dynamically determines from the Material Type, which Material Descriptions to use and generates the drop-down list for the Material Description Column.

You do not have to create any data validation lists for these two columns in question as they are added dynamically as needed.  Also, I've built this fairly generically, so you can add additional (similar) capability to other columns or another project.

Note, if there is no Material Type, the drop-down for Material Description will be the default setting of "Please Select A Material Type…".  Also, if there is no Material Description for an existing Material Type selected, there will be advice posted in the cell as well

I changed the formula for the Named Range, "Material Type" to:

='Material Database'!$B$2:INDEX('Material Database'!$B:$B,MATCH(REPT("Z",20),'Material Database'!$B:$B))

Open in new window


So, its a dynamic range, based on what data is in that column.

At this point, just download the file and see if its doing what you want it to do.  Come back here to see how I did it after you're satisfield :)
-------------------------------------------
 Testing-r3-publ.xls
------------------------------------------


I had originally leveraged all my bells and whistles, dyamically generating drop-downs based on the validation range using a combobox (e.g., if the range was MaterialType for that data entry, it would build a dynamic ComboBox on top with unique entries for selection) - or any range name for that matter.  Given your comments and the lenght of my documentation in explanation, I decided to keep it as straightforward as possible (though we can go here, later :)

This one checks column 2 and column 3 (with code provisions for adding more) to work Material Type and Material Description, and you can see the code at the bottom of the case statement which indicates more can be added in future.

Here's the sheet change code that gets pasted in the Estimating Template:
'The below tests for validation list and if so, calls routines to populate the data validation list
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim validRange As Boolean, checkRange As String
Set ws = ActiveSheet

Cancel = True

    Application.EnableEvents = False
    
    If Target.Count = 1 Then 'if > 1 then must be from multiple cell operation:  copy/paste or drag will have no operation
        Select Case Target.Column
        
            Case 2:  'Material Type Column
                Call loadUniqueValidationList(Target, ThisWorkbook.Worksheets("Material Database"), "MaterialType")
            Case 3:  'Material Description Column
                If Target.Offset(0, -1).Value <> "" Then 'and a valid Material Type was selected...
                    Call loadLookupMatchValidationList(Target, ThisWorkbook.Worksheets("Material Database"), "B:B")
                Else
                    Call loadValidationRange("Please Select Material Type...")
                End If
            Case Else:
                'etcetera, for any additional validation lists forthcoming
        End Select
    
    End If
    
    Application.EnableEvents = True
    
End Sub

Open in new window


Here's the code that has the routines and functions to make it all work:

The routine, loadUniqueValidationList() creates a list object (just a container for the list) and leverages the getListObjectUnique() and Qsort() routines (that came from the Article), builds a dictionary of unique items, sorts them, then loads the validation list using the loadValidationRange() routine.

The routine loadLookupMatchValidationList() looks at the column to the left for its values, and loads the validation range for that selection as well.

The loadUniqueValidationList parameters are such that sheet names, and columns for the list can be changed (or added for new validation ranges where you want unique lists).

The loadLookupMatchValidationList parameters are such that sheet names, and columns for the lookup/match can be changed (or added, as well)
 
Note - unlike normal validation lists, THESE will have code to support sorting, but I didn't sort in the lookup/match (Material Description) routine as it made sense to leave that unsorted

Sub loadUniqueValidationList(Target As Range, dbWks As Worksheet, keyCol As String)
Dim myRange As Range
Dim lngRow As Long, lngCol As Long
Dim listObject As New OLEObject 'just a container to hold the list
Dim uniqueListObjectList As Variant 'or Dictionary for early binding;  could be number or text
Dim myArray As Variant
Dim myDataValidationRange As String

    Set myRange = Range(keyCol)
    myRngofOptions = Application.Transpose(myRange) 'get the Material Type list from the range
    Set listObject = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1") 'temporary only
    'Set listObject = ActiveSheet.OLEObjects("TempCombo") 'temporary only - used for testing (re: add manually, comment out the above, then you can debug this module if needed)
    listObject.ListFillRange = "='" & dbWks.Name & "'!" & myRange.Address
    
    Set uniqueListObjectList = getListObjectUnique(listObject) ' ok - got the unique list in the dictionary
    
    'sort first, then set range string
    myArray = uniqueListObjectList.Keys
    Call QSort(myArray, LBound(myArray), UBound(myArray)) 'only if you want the output sorted
    For i = 0 To uniqueListObjectList.Count - 1 'now load the list object with sorted array
        If myArray(i) <> "" Then
            myDataValidationRange = myArray(i) & "," & myDataValidationRange
        End If
    Next i
    
    listObject.Visible = False 'hide combobox
    listObject.Delete 'get rid of it
    myDataValidationRange = Left(myDataValidationRange, Len(myDataValidationRange) - 1) 'remove the last comma
    'now load data validation list with results from list object
    Call loadValidationRange(myDataValidationRange)
End Sub

Sub loadLookupMatchValidationList(Target As Range, dbWks As Worksheet, keyCol As String) 'can be used for other lookups - looks to the left and then searches database
Dim myRngofOptions As String, myArrayOfOptions() As String, firstAddress As String
Dim myMatchRange As Range, i As Integer

     'navigate through database and build unique list into an array name - needs to be here for scenario 1 (one option, anyway), and in sheet change on scenario 2
     i = 0
     Set dbWks = ThisWorkbook.Worksheets("Material Database")
     With dbWks.Range(keyCol) 'look in Materials Type to find matches
         Set myMatchRange = .Find(Target.Offset(0, -1).Value, LookIn:=xlValues) 'look to the left of the selected cell, to determine Material Type, and search for it in the database
         If Not myMatchRange Is Nothing Then 'based on what's found, build a comma-separated string list of dropdown options
             firstAddress = myMatchRange.Address 'grab the first address, for testing in the loop
             myRngofOptions = myMatchRange.Offset(0, -1).Value 'get description from the first match (to the left of Material Type)
             i = i + 1
             Do
                 Set myMatchRange = .FindNext(myMatchRange) 'get the next match, if any
                 If Not myMatchRange Is Nothing And myMatchRange.Address <> firstAddress Then 'if there is a match, add it to the comma-separated list
                     myRngofOptions = myRngofOptions & "," & myMatchRange.Offset(0, -1).Value
                 End If
                 
             Loop While Not myMatchRange Is Nothing And myMatchRange.Address <> firstAddress 'do this until all matches are found
             
            'now set the data validation range
            If myRngofOptions <> "" Then
                Call loadValidationRange(myRngofOptions) 'keep this line though :)
            Else
                Target.Value = "No Match Found - Please check Material Type in database..."
            End If
         End If
    End With
End Sub

Sub loadValidationRange(rngOpts As String)
    If rngOpts <> "" Then
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=rngOpts
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    End If
End Sub

Function getListObjectUnique(listObject As Object) As Variant
Dim uniqueList As Variant 'or dictionary if early binding
Dim myItem As Variant 'for the list currently in the list object
Dim i As Long

    Set uniqueList = CreateObject("Scripting.Dictionary") 'or = New Scripting Dictionary for early binding

    
    For i = 0 To listObject.Object.ListCount - 1 'go through the current list and add them to the dictionary (dictionary won't allow duplicates)
        If Not uniqueList.Exists(listObject.Object.List(i)) Then 'ignore duplicate "keys"
            uniqueList.Add listObject.Object.List(i), i 'item count is the key, and the value is the key as well, for easy retrieval
        End If
    Next i
    
    Set getListObjectUnique = uniqueList
End Function

Sub QSort(sortArray As Variant, ByVal leftIndex As Integer, ByVal rightIndex As Integer)
    Dim compValue As Variant
    Dim i As Integer
    Dim J As Integer
    Dim tempVar As Variant

    i = leftIndex
    J = rightIndex
    
    compValue = sortArray(Int((i + J) / 2))

    Do
        Do While (sortArray(i) < compValue And i < rightIndex)
            i = i + 1
        Loop
        Do While (compValue < sortArray(J) And J > leftIndex)
            J = J - 1
        Loop
        If i <= J Then
        
            tempVar = sortArray(i)
            sortArray(i) = sortArray(J)
            sortArray(J) = tempVar
            
            i = i + 1
            J = J - 1
        End If
    Loop While i <= J

    If leftIndex < J Then QSort sortArray, leftIndex, J
    If i < rightIndex Then QSort sortArray, i, rightIndex
End Sub

Open in new window


Also, note, we're using the dictionary scripting library (for late binding so no library reference in VBA Editor is required)  

I've also attached the demonstration file with everything installed and "ready to go".
Enjoy!  And, feel free to ask any questions.  I'm always happy to help!

If you did find this useful, please check out the article and vote whether you thought that was helpful!

Thanks!

Dave    
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DadXSixAuthor Commented:
Dave,

Thanks for the reply and the example template.  I'm reviewing it now and will respond with comments as soon as i have finished.  So far this looks like the trick i was looking for.  

@krishnakrkc

While i appreciate your response, i'm not a VBA programmer as stated in my question.  I can dissect the most complicated code (given enough time), however compiling it is another story.   Thank you for taking the time to write what you did though! :)
0
DadXSixAuthor Commented:
Dave,

Works Beautifully!  Now onto some of the fine tuning!

1. How do i adjust the data validation to allow for entry other than what is represented in the 'Database' proper?  For example, I will be importing data from another excel sheet that will be used to populate the 'Estimate' tab.   Once populated with my generic data, i will then use the drop downs to enter exact product info over the populated line.  (see attached example for more details)

2.  As i said in my question, im not arrogant enough to consider myself anything close to a VBA programmer, so it will take me awhile to decipher what you've got shown as far as the sheet code goes.  Can you dumb it down a bit more so that i can make changes as needed to accommodate additional information as required?

3. Is the 'Material Type' list that's generated, pulled from the 'Material Type' column in the Database worksheet?  The information in that list can be dynamic, correct?

4. Will i encounter any problems adding data to the Database tab, such as additional rows and/or columns (The database itself will consist of about 15-20k unique items)?

5. Are the headers called, in the Database tab, static?  Can i move them around on the table if needed?


If any of my questions sound layman, then welcome to my world! :)  This little project, as simple as it sounds, is going to create quite a bit of change at work.  I want to understand everything i can about whats being done so that i have the ability to explain/change and incorporate.  

Testing-r3-publ-w-comments.xls
0
krishnakrkcCommented:
Hi,

For your reference, please find attached.

Kris
Testing.zip
0
dlmilleCommented:
@DadXSix - no worries...

>>1. How do i adjust the data validation to allow for entry other than what is represented in the 'Database' proper?  For example, I will be importing data from another excel sheet that will be used to populate the 'Estimate' tab.   Once populated with my generic data, i will then use the drop downs to enter exact product info over the populated line.  (see attached example for more details)

The dropdowns populate based on what gets imported.  Just import data and when you click in the Material Type or Description fields, data will dynamically populate from the Material Database tab.  PS - I'm not seeing too many comments in the attached file...

2.  As i said in my question, im not arrogant enough to consider myself anything close to a VBA programmer, so it will take me awhile to decipher what you've got shown as far as the sheet code goes.  Can you dumb it down a bit more so that i can make changes as needed to accommodate additional information as required?

I'll try - but it would be useful for me to address specific questions after this as I don't know what gaps I should be addressing.....

The way I set it up, really the only thing you need to be concerned with is what is pasted in the sheet code page called Estimating Template as it controls all the action.  See more documentation in the embedded code, below.  I hope this is sufficient to give you comfort so you can test making changes, now - even before you start loading new data..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim validRange As Boolean, checkRange As String
Set ws = ActiveSheet

Cancel = True

    Application.EnableEvents = False 'avoids recursive looping as we're going to make changes so don't want those changes calling this routine again and again
    
    If Target.Count = 1 Then 'if > 1 then must be from multiple cell operation:  copy/paste or drag will have no operation
        Select Case Target.Column
        
            Case 2:  'Material Type Column (change this number if you move the Material Type column in the Estimating Template tab
                Call loadUniqueValidationList(Target, ThisWorkbook.Worksheets("Material Database"), "MaterialType")
            Case 3:  'Material Description Column (always make sure the Material Description column is just to the right of the Material Type column, and that the Case Number (e.g., Case 3) is one higher than Material Type column.
                If Target.Offset(0, -1).Value <> "" Then 'and a valid Material Type was selected...

                    'this checks one column to the left for Material Type, and then calls the function that looks in the Material Database in column "B:B" for Material Type (and then one left for material Description).  If you move Material Type/Material Description, change the "B:B" to whatever column Material type will reside in the Database (and make sure the Description column is one to the LEFT of that column.
                    Call loadLookupMatchValidationList(Target, ThisWorkbook.Worksheets("Material Database"), "B:B")
                Else
                    Call loadValidationRange("Please Select Material Type...")
                End If
            Case Else:
                'etcetera, for any additional validation lists forthcoming
        End Select
    
    End If
    
    Application.EnableEvents = True
    
End Sub

Open in new window


3. Is the 'Material Type' list that's generated, pulled from the 'Material Type' column in the Database worksheet?  The information in that list can be dynamic, correct?

That is correct - both Material Type and Material Description pull dynamically from the Database worksheet - why don't you just check right now - add a new Material type, then go to the Estimating Template and try a drop down - that's what I did by adding "dave" and "test" in the Materials Database to demonstrate that.


4. Will i encounter any problems adding data to the Database tab, such as additional rows and/or columns (The database itself will consist of about 15-20k unique items)?

You should not encounter problems adding additional rows.  The MaterialType named range (that tells this little utility where to do data validation from) is

='Material Database'!$B$2:INDEX('Material Database'!$B:$B,MATCH(REPT("Z",20),'Material Database'!$B:$B))

This looks from B2 to the end of any data entry.  Adding additional columns to the left would require code changes (E.g., the column being tested for in the code that's in the Estimating Template sheet codepage.  Just make sure that the Material Description is just to the right of Material Type and the lookup function I created will work.  If you add columns to the right of Material Description, no problems should arise.

Note - if you move where MaterialType resides, you'll need to change this formula (from $B:$B to the new column, keeping the description field one column to the left.

Also in the Material Database  - Material Type is defined by the named range - just make sure Material Description is always to the LEFT, this time, and you can move those two columns as you like.  Anything to the right of Material Type in the Materials Database should create no problems.

As always, if you have any problems, don't hesitate to post here with the issue/question and I'll respond, readily.

5. Are the headers called, in the Database tab, static?  Can i move them around on the table if needed?

I think I answered this in Question 4.  And, you can certainly change any header names.

Why don't you manually make some changes and see how it behaves?  You don't have to wait to read new data to do that.

PS - for some reason, I made some very minor change and didn't post that.  I'm reposting - just to be sure you have the latest.

Sometimes with processes that turn EVENTS OFF, if there's a problem, they stay off instead of turning back on.  I'm putting a button on the Template you can hit if things don't seem to be working - all it will do is ensure EVENTS are ON.

Cheers,

Dave
Testing-r4-publ.xls
0
DadXSixAuthor Commented:
Dave went above and beyond to help me with this issue.  A true asset to the EE Team.  Keep up the awesome work Dave!
0
DadXSixAuthor Commented:
Dave,

I have a second part to this question that i would like to invite you to undertake.  Since you were essentially the architect to this part, the second part should be a no brainer for you.
0
dlmilleCommented:
ok - ask a RELATED QUESTION (See link on this one) so others will have this as a reference for support or to help you, as well.

:)

Dave
0
dlmilleCommented:
Whoops - this one has the Enable Events button.  It shouldn't be needed, but stuff happens, right?  I don't know how many times in early development I beat my head against the wall and didn't realize my code wasn't running because there was an error between turning events off and back on, lol. Keep the button around for a while, but you won't need it in future. The first thing to ask when things aren't working some times is, "did my code turn events off?".  closing and opening the spreadsheet will do that too :) but that's overkill.

So, anytime I use code that messes with events, I try to have an Enable Events directive in buttons somewhere (e.g., you might have a load database button in the future - stick the code in there, and other places where there are buttons, and you may never know they had been left off.

Again - a bit overkill for this app, but if you're a developing developer, like me, its good to have these and other techniques in your "back pocket" :)

Enjoy!

Dave
Testing-r5-publ.xls
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.