cascading dynamic unique drop down list

Hi,

I'm trying to implement a series of cascading/dynamic drop down lists (once var1 is selected, only unique relevent options should be displayed for var2, then var3 options should depend on var1&var2, etc....), which once all selected display data in the output section.

If I enter the input data manually the correct data is displayed. It is the dropdown lists I'm having problems with (using DGET for this).

I found this thread and tried to follow it by adapting it to my spreadsheet with not much success so far:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24467696.html#mainSection

I've attached a sample of my attempt with the integrated code taken from the link above. The data on "master" is close to what I'm really going to use (ie several spaces and illegal characters used). Each row on master sheet has a unique set of variables.

Thanks for the help.
form-example-16-04-2010.xls
miky_517Asked:
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.

Ardhendu SarangiSr. Project ManagerCommented:
Hi miky,

There is an excellent tutorial available online at contextures.com. Here is the link to that.

http://www.contextures.com/xlDataVal02.html

I was trying to apply that to your spreadsheet however it doesn't seem that you have unique values for var2, var3 and var4.

So I am a bit confused as to how do you want the lists to flow?

Say for example, you select Var1 as A, you want to show 2x AA and 2x BB in var2 list ? Can you please elaborate?

Thanks,
Ardhendu


0
miky_517Author Commented:
Hi,
You're right the data in each column is nearly random. There is no way to make them dependent as in the tutorial (I may have used the wrong terminology sorry). In theory I could end up with hundreds (or thousands) of unique combinations with the 5 variables.
0
alainbrydenCommented:
Here you go, I've got it all worked out.

The best trick is to take advantage of Excel's autofilter. Start by setting the variables that the user has selected, and the use advanced filter to get the distinct possible values remaining in each of the other columns.

The attached file does this, and as a bonus, it even auto selects from the drop down all variables that only have on possible value. The solution is snappy and effective.

--
Alain
Private Sub Worksheet_Change(ByVal Target As Range)
    Const InputRow = 6
    Dim targ As Range
    
    Set targ = Union([A6], [B6], [C6], [D6], [E6])
    If Intersect(targ, Target) Is Nothing Then Exit Sub
     
    Application.EnableEvents = False 'Prevent changes we make in this sheet from causing a loop'
    Application.ScreenUpdating = False 'Makes everything faster'
    
    Dim colnum As Integer, i As Integer, j As Integer
    colnum = Target.Column
    
    'Clear dependant fields
    For i = colnum + 1 To 5
        Sheet1.Cells(InputRow, i) = vbNullString
    Next i
    
    'Repopulate the options ranges for the rest of the listboxes'
    For i = colnum + 1 To 5
        'First filter the list to only entries that meet the current requirements'
        If Sheet2.FilterMode = True Then Sheet2.ShowAllData
        For j = 1 To colnum
            Sheet2.Range("$A:$H").AutoFilter Field:=j, Criteria1:=Sheet1.Cells(InputRow, j)
        Next j
        ListBoxHelper.GetDistinctValues i
    Next i

    'Auto-Set remaining fields if there's only 1 option'
    For i = colnum + 1 To 5
        If Sheet3.Range("Var" & i).Count = 1 Then Sheet1.Cells(InputRow, i) = Sheet3.Range("Var" & i).Value
    Next i

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Sub GetDistinctValues(ByVal colnum As Integer)
    Sheet3.Columns(colnum).Clear
    Sheet3.Columns(colnum + 6).Clear
    Sheet2.Range(Sheet2.Cells(1, colnum), Sheet2.Cells(1, colnum).End(xlDown)).Copy Sheet3.Cells(1, colnum + 6)
    Sheet3.Columns(colnum + 6).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheet3.Range(Sheet3.Cells(2, colnum + 6), Sheet3.Cells(1, colnum + 6).End(xlDown)).Copy Sheet3.Cells(1, colnum)
    If Sheet3.AutoFilterMode = True Then Sheet3.ShowAllData
    Sheet3.Columns(colnum + 6).Clear
    If Sheet3.Cells(2, colnum) = vbNullString Then
        ThisWorkbook.Names("Var" & colnum).RefersTo = Sheet3.Cells(1, colnum)
    Else
        ThisWorkbook.Names("Var" & colnum).RefersTo = Sheet3.Range(Sheet3.Cells(1, colnum), Sheet3.Cells(1, colnum).End(xlDown))
    End If
End Sub

Open in new window

img1.PNG
img2.PNG
img3.PNG
Auto-Populate-Drop-Boxes.xls
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

miky_517Author Commented:
Hi Alain,

That seems to work in its current form, thanks !

Just a few questions for me to understand whether I can adapt it to my real worksheet.

1.Is it necessary to autofilter the master sheet in order to get the dropdown menus to work ? I'd like to be able to look at the entire raw data on the master sheet at anytime (either to look for data or to add/update rows). The filtering seems to really make my CPU work hard (it's a 4 year laptop, i know...) but I am conscious that some people using it may even have an older machine.

2. My final workbook will have 2 "input" sections with 2 "output" on the main sheet to allow comparaison of the data. Will anything stop me from duplicating it to work ? Apologies I should have created my sample form in this way. Let me know if I should re-work it to make it clearer.

Thanks.
0
alainbrydenCommented:
The autofilter is just temporary. You'll notice that it actually just copies the filtered cells to a third sheet and then restores the second sheet to it's normal state - no no worries there.

As for the filtering time, it may seem pretty steep now, but it doesn't scale much with data size, so you pretty much don't have to worry about it being any worse than it is.

There's no problem with having just 2 input sections. You'll notice that there's a few places where I reference the number of inputs there are. The can make less and still have it work about the same, you just need to adjust the code in minor ways. It'll also probably go much faster with just 2 inputs, since it's not filtering 5 times in a row.

--
Alain
0
miky_517Author Commented:
What I meant by 2 inputs was actually 2x5 variables (will probably be 2x6 in my final workbook). I've modified the file you re-attached to reflect the double input/output. I am not sure now how to adapt it to make this work. Your expertise would be appreciated again. Thanks.
Auto-Populate-Drop-Boxes---dual-.xls
0
alainbrydenCommented:
Hmm... I'm getting dental surgery today (skateboarding accident), so I might be out of commission for a little while. To ensure you get help as fast as possible, perhaps you should close this question and start a new one to give someone else the chance to jump in and adapt my workbook. It should be pretty easy for them to figure out.

Also post a link to the follow-up question in here so that I can check on it when I get over this is make sure it gets answered one way or another.

Thanks!

Alain
0
miky_517Author Commented:
Oops...I will do thanks. Good luck with the dentist.
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.