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
LVL 1
xllvrAsked:
Who is Participating?
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.

Saurabh Singh TeotiaCommented:
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...
0
Martin LissOlder than dirtCommented:
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).
0
xllvrAuthor Commented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

xllvrAuthor Commented:
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)
0
Saurabh Singh TeotiaCommented:
Their you go i wrote a macro for you to do what you are looking for...The way macro works is as soon you enter a value in Column-A i mean type ..it will automatically show matching validation of that type in account category..and now as soon you select the account category it will show you a matching validation of it in description for you to choose from...

Saurabh...
EE-DependentDropdown-1.xlsm
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
xllvrAuthor Commented:
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.)
0
Saurabh Singh TeotiaCommented:
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...
0
xllvrAuthor Commented:
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
0
Saurabh Singh TeotiaCommented:
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...
0
xllvrAuthor Commented:
Thanks for all the support on this, Saurabh.  Great work!
0
Saurabh Singh TeotiaCommented:
Nancy..You are welcome..Glad to Help .. :-)
0
xllvrAuthor Commented:
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?

Error message
0
xllvrAuthor Commented:
Perhaps I should attach the file itself.
EE-DependentDropdown-V2.xlsm
0
Martin LissOlder than dirtCommented:
I was able to open the V2 file without any problem.
0
xllvrAuthor Commented:
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:Error on open
Thanks for chiming in, Martin.  So nice of you!
EE-DependentDropdown.xlsm
0
Saurabh Singh TeotiaCommented:
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..
0
xllvrAuthor Commented:
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.  :)
0
Martin LissOlder than dirtCommented:
The macros that Saurabh wrote are Volatile. At the start of Workbook_Open try Application.Volatile False
0
xllvrAuthor Commented:
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
0
Martin LissOlder than dirtCommented:
I don't need any points for what I contributed here.
0
xllvrAuthor Commented:
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?
0
Saurabh Singh TeotiaCommented:
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...
0
xllvrAuthor Commented:
That would be amazing.  Sounds like a great idea.  Thank you so much!
0
Saurabh Singh TeotiaCommented:
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
0
xllvrAuthor Commented:
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!
0
Saurabh Singh TeotiaCommented:
Nancy,

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

Saurabh..
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.