asked on

Dependent drop-down list based on Excel table

Hi Experts,

I'm having trouble coming up with a solid solution for the attached. I know how to use data validation and range references to create dependent drop-down lists from small amounts of source data, however, the my source data is multi-layered and requires a lot of extra table and range name creation via the method I know how to use.

Can you view the two tabs in the attached and offer a solution? One tab has the source data (LIST) and the 2nd tab is where the drop downs need to live. I have tried variations of OFFSET with COUNTIF with wonky results.

As always, thanks for your help!
EE-DependentDropdown.xlsm

Saurabh Singh Teotia

Quick Question are you open for a VBA solution?? The reason i ask that since for account you have multiple same values repeated which are duplicate in nature through vb..I can make it unique....I understand that's their because of your description.. if i create it by formula this will be tricky to deal with to filter unique entries first..

Let me know your thoughts and if you are fine will design a vb solution...

Martin Liss

I downloaded your workbook but I need more information about what you need. For example I'd like to know where the dropdown should be located and exactly what should appear in the list (and why if appropriate).

xllvr

ASKER

Saurabh: Aha...you see my problem. No wonder I was getting an odd result. I'm open to vba if formulas are out of the question but get concerned if it's too complex for either my skill level or the person I'm doing this for. Thanks for putting some brain power behind this.

xllvr

ASKER

Martin: On the DATA TABLE tab I highlighted the columns with red text where I will need drop-down lists. This was my note in the file on the LIST tab:

Red fill or red text = Fields that require dependent drop-down lists on DATA TABLE tab (e.g., Description is a subset of/dependent on Account, Account is a subset of/dependent on Type)

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xllvr

ASKER

Hi Saurabh,

Thanks so much for the slick solution! I think I've got it working well in my actual workbook (sent you a mock up). I think the only thing that might be sticky is whether the list can be alpha sorted so the user doesn't have to hunt around in each drop down? Is that even possible? Let me know. Thank you!

(Apologies for being in and out of touch. Am in between meetings.)

Saurabh Singh Teotia

Xllvr,

The best way i will recommend is sort it originally in the data itself on the account category and then on description as you need to do that activity only once.. And once the original data is sorted which act as a input these values which you will get in validation will automatically be sorted...

Saurabh...

xllvr

ASKER

Saurabh...great idea. Just did what you suggested and it worked well. One last question. Can I change the delimiter from a comma to a semicolon in case the user inputs an account name or description that uses a comma? See code below. If I'm not mistaken it would be in the following portion of the code and it appears twice: getlist1 = getlist1 & "," & Trim(ct)

Option Explicit

Function getlist1(rng As Range, r1 As Range, z As Long)
    Application.Volatile

    Dim cell As Range, col As Collection
    Dim r As Range, ct As Variant
    Set col = New Collection

    On Error Resume Next
    For Each cell In r1

        If Trim(UCase(cell.Value)) = Trim(UCase(rng.Value)) Then
            col.Add cell.Offset(0, z).Value, CStr(cell.Offset(0, z).Value)
        End If
    Next cell

    For Each ct In col
        If getlist1 = "" Then
            getlist1 = Trim(ct)
        Else
            getlist1 = getlist1 & "," & Trim(ct)
        End If

    Next ct
    
     If getlist1 = "" Then getlist1 = " "
End Function


Function getlist2(rk As String, r1 As Range, r2 As String, z As Long, k As Long)
    Application.Volatile

    Dim cell As Range, col As Collection
    Dim r As Range, ct As Variant
    Set col = New Collection

    On Error Resume Next
    For Each cell In r1

        If Trim(UCase(cell.Value)) = Trim(UCase(rk)) And Trim(UCase(cell.Offset(0, z).Value)) = Trim(UCase(r2)) Then
            col.Add cell.Offset(0, k).Value, CStr(cell.Offset(0, k).Value)
        End If
    Next cell

    For Each ct In col
        If getlist2 = "" Then
            getlist2 = Trim(ct)
        Else
            getlist2 = getlist2 & "," & Trim(ct)
        End If

    Next ct
    
    If getlist2 = "" Then getlist2 = " "
End Function

Open in new window

Once I get your answer, I'm happy to close this out. Wish I could award more points!

A million thanks,
Nancy

Saurabh Singh Teotia

Ahh you need to make sure they don't enter comma, Because if you notice in data validation comma act as delimiter so that's a limitation in excel only and you can't change that and you need to advise the user as well not use comma as that will show that item as another item in validation...

xllvr

ASKER

Thanks for all the support on this, Saurabh. Great work!

Saurabh Singh Teotia

Nancy..You are welcome..Glad to Help .. :-)

xllvr

ASKER

Oh no! I thought we were good to go, but one issue has cropped up. I saved the file and re-opened it and Microsoft asked if I wanted to repair the file and then gave this error message. I apologize for not anticipating something happening upon save and open. Am I able to open this up as a new question and aware you additional points?

xllvr

ASKER

Perhaps I should attach the file itself.
EE-DependentDropdown-V2.xlsm

Martin Liss

I was able to open the V2 file without any problem.

xllvr

ASKER

I'm not sure what I did to it to have it operate properly. I was just able to open it as well. I just tried it again with the original file...attached here...and got this error message:

Thanks for chiming in, Martin. So nice of you!
EE-DependentDropdown.xlsm

Saurabh Singh Teotia

Just Click Yes..because what i'm finding the major reason for this is the data validation and at times i have found that if you have make validation by excel macro like in this case..it gives that xml error..

xllvr

ASKER

I've saved/closed the actual file I'm using (with real data) and then reopened with the error message clicked "Yes." I closed it again and reopened and got same error. Does this mean it will happen every time it gets opened? It might freak out the person I'm doing this for. :)

Martin Liss

The macros that Saurabh wrote are Volatile. At the start of Workbook_Open try Application.Volatile False

xllvr

ASKER

Martin & Saurabh,

I so appreciate the help. I tried adding "False" to that part of the code but there was no change. I still can't save without it wanting to Save As, and I still can't open without the two different dialog boxes/error messages appearing. I don't think I can use this code after all.

Would it make sense to submit a new question so I can award points for the additional effort on both your parts?

Let me know. Many thanks!

Nancy

Martin Liss

I don't need any points for what I contributed here.

xllvr

ASKER

Apologies if that wasn't clear. I can't use the code as is and am wondering if I need to re-open the issue as a new question. The code worked great while in the file. That's when I accepted the solution/code Saurabh supplied. It wasn't until I closed the file and got back in that the two error messages popped up in addition to defaulting to Save As vs. Save. I'm not sure what is appropriate when something like this happens. Do users typically open a new question?

Saurabh Singh Teotia

Nancy,

You know what you are getting that error message because i'm creating validation by vba..If you are open what i can do is design a solution where it remove validation when you try to save it or close it and then again create your validation when you open it back..That way it will take care of the error message that you are getting here..

Let me know if you want to test that...

Saurabh...

xllvr

ASKER

That would be amazing. Sounds like a great idea. Thank you so much!

Saurabh Singh Teotia

Nancy,

Their you go i added 2 more codes in your thisworkbook module which will take care of your problem what you were dealing with..

Saurabh...
EE-DependentDropdown-1.xlsm

xllvr

ASKER

A million thanks! That did the trick. I apologize for not completely closing out the file and re-opening prior to accepting the solution. Thanks for the extra effort!

Saurabh Singh Teotia

Nancy,

Glad to help..And Nah it's cool.. :-)

Saurabh..