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?
 
SiddharthRoutConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

All Courses

From novice to tech pro — start learning today.