Dynamic Table Lookup Sheet

I have a spreadsheet (attached) where I need to be able to select either Type or Category and if so it will show the appropriate Question or Questions associated with the partial or full selection.  The selection should be a drop down box that only shows the selections that are relivant once the first selection is made.

So if you select Type, it will display (to the right) all the questions in that "type".  If you then select Category, it only shows the questions (a subset) that match up to both criteria.  You must be able to look up the other way as well (i.e. select Category, than Type).
Dynamic-Selection-Table-Lookup.xlsm
Bright01Asked:
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.

SiddharthRoutCommented:
Quick question.

If your Type is A and Cat is 1 then which question do you want to show in the Yellow area as there are duplicate values in Col C and Col D?

Sid
0
Bright01Author Commented:
Sid,

Greetings!   If Type A is selected first, in the dynamic area Questions 1:Questions9.  Then when you select Cat. 1, the Questions subset to Questions 1: Questions3 (the others go away).  Subsiquently, if you first enter (drop down box) Cat. 1, Questions 1, 2,3, 10, 16, 17 and 18 appear.  Then when you select Type B, the Questions subset to Question 10 only (the others disappear).

Does that help?

B.  
0
SiddharthRoutCommented:
Is this what you want? Sample Attached

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim LastRow As Long, LastRowI As Long, i As Long
    
    If Not Intersect(Target, Range("G2")) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Range("I8:I17").ClearContents
        LastRowI = Range("I" & Rows.Count).End(xlUp).Row + 1
        LastRow = Range("C" & Rows.Count).End(xlUp).Row
        If Range("G3").Value = "" Then
            For i = 8 To LastRow
                If Range("C" & i).Value = Target.Value Then
                    Range("I" & LastRowI).Value = Range("E" & i).Value
                    LastRowI = LastRowI + 1
                End If
            Next i
        Else
            For i = 8 To LastRow
                If Range("C" & i).Value = Target.Value And _
                Range("D" & i).Value = Range("G3").Value Then
                    Range("I" & LastRowI).Value = Range("E" & i).Value
                    LastRowI = LastRowI + 1
                End If
            Next i
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    ElseIf Not Intersect(Target, Range("G3")) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Range("I8:I17").ClearContents
        LastRowI = Range("I" & Rows.Count).End(xlUp).Row + 1
        LastRow = Range("C" & Rows.Count).End(xlUp).Row
        If Range("G2").Value = "" Then
            For i = 8 To LastRow
                If Range("D" & i).Value = Target.Value Then
                    Range("I" & LastRowI).Value = Range("E" & i).Value
                    LastRowI = LastRowI + 1
                End If
            Next i
        Else
            For i = 8 To LastRow
                If Range("D" & i).Value = Target.Value And _
                Range("C" & i).Value = Range("G2").Value Then
                    Range("I" & LastRowI).Value = Range("E" & i).Value
                    LastRowI = LastRowI + 1
                End If
            Next i
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Open in new window


Sample.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
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Bright01Author Commented:
Fantastic!!!  Sid, thank you ........ I'll be asking a related question shortly to enhance it further.  EXCELLENT WORK.

B.
0
SiddharthRoutCommented:
Glad to be of help :)

Sid
0
Bright01Author Commented:
Sid,

Quick question; On Data Validation, if I have a list that has repeatable variables what formula do I use to have the dropdown box list the variables but not repeat them?  For example,
A
A
A
B
B
C
C
C
C
Then in the drop down:
A
B
C

0
Bright01Author Commented:
Wow..... that's really complex.  I'm a below average, closet technical guy.  I'm going to have to craft a related question and see if I can get assistance on implementing this in the drop down boxes.
0
dlmilleCommented:
Bright01 - sorry it appears complex (and I thought "and a little code" would lower any intimidation).  Its actually quite easy.

Ask a related question and post your xslm file and I'll step you through it (and also put the solution in your spreadsheet).

Dave
0
dlmilleCommented:
Bright01 - Apologies:  I didn't mean to insinuate that folks should have thought it was easy.  I was accepting your comment that perhaps the way I presented it could be intimidating.  Going forward I'm going to post "how to do this" first, before I get into the tecky details that folks don't really need to understand unless they have interest, etc.


I would be happy to assist you in eliminating duplicates with this drop down technique at some point in the future should you choose to kick off a related question.

Cheers,

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