Excel drop down list HELP

Hello All,

Basically I have a table with 2 columns, column A contain the company name and column B contains an items list

Company A - Widget 1
Company A - Widget 2
Company B - Widget 3
Company B - Widget 4

I would like to setup a drop down list that references a company name from another cell and lists all the items for that company.

A2 = Company A
Drop down list = Widget 1, Widget 2.

I've played around with and Index Match formula but it only returns the first result and not the array.  If I create a named range using the Index Match formula, i'm unable to assign the named range to data validation.

Looking for best way to accomplish this.

Thanks!
LVL 1
sguidoAsked:
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.

Martin LissOlder than dirtCommented:
If I create a named range using the Index Match formula, i'm unable to assign the named range to data validation.
What happens when you try it?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you probably can try this... but it's a bit complicated but it works

1. Create a Data page, put in the data that you need to be populated as the list (pls refer to sheet: Data)

2. in Sheet: Data, we create a temporary column at Column C, to get the items to be used to create the first level unique dropdown. The formula are put into this column, so when it's needed, just drag down accordingly to cater for more items.

3. Make list in Column C as a List. You can found the list with range in Name Manager. (Formulas > Name Manager), for example:

Create a Name: List1 with range: =Data!$C$2:$C$100

4. Back to the sheet you wish to have the dropdown list. We create the first level dropdown list by putting Data Validation Rule:

=OFFSET(List1, 0, 0, COUNT(IF(List1="", "", 1)), 1)

Make sure you select the "List" in Allow option.

5. We need to add the macro to detect the change of cells in first level dropdown, so that the second dropdown list can be populated accordingly.

Hence, we need add the macro (VBA) codes in the target sheet's Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim c As Range
    Dim dic As Scripting.Dictionary
    Dim t As String
    Dim FirstAddress As String
    
    If Target.Column = 1 And Target.Row > 1 Then
        Cells(Target.Row, Target.Column + 1) = ""
        Cells(Target.Row, Target.Column + 1).Select
        If Cells(Target.Row, Target.Column) = "" Then
            Set c = Cells(Target.Row, Target.Column + 1)
            c.Validation.Delete
            Exit Sub
        End If
        
        With Worksheets("Data").Range("A2:B1000")
            Set r = .Find(What:=Cells(Target.Row, Target.Column), _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
            If Not r Is Nothing Then
                Set dic = CreateObject("Scripting.Dictionary")
                
                FirstAddress = r.Address
                Do
                    If dic.Exists(r.Offset(0, 1).Value) = False Then
                        dic.Add r.Offset(0, 1).Value, r.Offset(0, 1).Value
                    End If
                    Set r = .FindNext(r)
                Loop While Not r Is Nothing And r.Address <> FirstAddress
            End If
        End With
        If FirstAddress <> "" Then
            Set c = Cells(Target.Row, Target.Column + 1)
            c.Validation.Delete
            For i = 0 To dic.Count - 1
                If i = 0 Then
                    t = dic.items(i)
                Else
                    t = t & "," & dic.items(i)
                End If
            Next
            c.Validation.Add xlValidateList, , , t
        Else
            MsgBox "Item entered is invalid", vbExclamation, "Item not found"
            Cells(Target.Row, Target.Column).Select
            Set c = Cells(Target.Row, Target.Column + 1)
            c.Validation.Delete
        End If
    End If
End Sub

Open in new window


remember to add in the references of Microsoft Scripting Runtime object library as we're using Scripting.Dictionary in our codes.

There are room for improvements depends on real scenarios.

pls customize accordingly.
28815159.xlsm

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
Roy CoxGroup Finance ManagerCommented:
Have you tried AdvancedFilter?

I've added the filter to the above example for you to try. You can amend the code to copy to a different sheet if required
JavaScript Best Practices

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

psteffCommented:
Here is an alternative NOT using VBA, but using a helper column if need be (if the company name has spaces in it).

If the company has blank spaces in it, you could use a helper column to convert the company name to a company code name without spaces.  From there you can set up named ranges based on the company code name for your drop down lists.  (If your company names do not have blank spaces, then you can skip the helper column and the vlookup).

The data validation then uses the indirect function to reference the company name (or code name) to get the correct named range for the drop down list.

Please see attached.  Let me know if you need further explanation.
DropDownList.xlsx
Roy CoxGroup Finance ManagerCommented:
I forgot to add to my post that the VBA is not necessary, but the filter can be run manually.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.