Using an Index number to generate a table in excel?

I have a combo box (combo box 1) which is currently linked to cell A1 in a given sheet.

A1 is showing an index number that changes when the value of the combo box changes.

I have another sheet in the same workbook that has a table using this index.

it has 1000's of rows and x number of columns.

I'd like to show a table of each row that has this index on the same sheet that has the combo box.

sanjangeorgeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SiddharthRoutConnect With a Mentor Commented:
If there won't be duplicate values then try this :)

Sample File Attached. All you need to do is manually change the values in Cell A1

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Dim ws1 As Worksheet, ws2 As Worksheet
        Dim i As Long, ws1LastRow As Long, ws2LastRow As Long
        Dim matchIndex As String, AllCars As String
        
        On Error GoTo Whoa
        
        Set ws1 = ActiveSheet
        Set ws2 = Sheets("Sheet1")
        ws2LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row
        
        ws1.Range("A12:E" & Rows.Count).ClearContents
        
        matchIndex = Target.Value
        
        With ws2
            .Range("$A$1:$E$" & ws2LastRow).AutoFilter Field:=1, Criteria1:=matchIndex
            .UsedRange.Offset(1, 0).Resize(.UsedRange.Rows.Count - 1).Rows.Copy _
            ws1.Range("A12")
            .Range("$A$1:$E$" & ws2LastRow).AutoFilter
        End With
        
        ws1LastRow = ws1.Range("B" & Rows.Count).End(xlUp).Row
        
        For i = 12 To ws1LastRow
            AllCars = AllCars & ", " & ws1.Range("C" & i).Value
        Next i
        
        ws1.Range("G3").Value = Mid(AllCars, 2)
    End If
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window

Index-Numbers.xls
0
 
SiddharthRoutCommented:
May I see a sample file so that I can get my references correct?

Sid
0
 
sanjangeorgeAuthor Commented:
Here you go Sid
ExampleForTables.xls
0
 
MeLindaJohnsonCommented:
Could you do this as a pivot table?
0
 
SiddharthRoutCommented:
The code is almost ready. Quick question. Will there be duplicates values in Sheet1?

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