Excel - How populate a column from another sheet b

Question

This cant be difficult but I just can't see how to do it.

I have Sheet 1 with i.e 3 empty columns, the top row is a data validation dropdown list of i.e. 50 unique header names present in Sheet 2.

Under each of the 50 headers in Sheet 2 there is an unknown number of rows of data.

From each of the 3 dropdown menus in Sheet 1, I simply want to populate that column with all data under that column header in Sheet 2.

Is there a VBA solution?

Answer 1

You don't need VBA for that, just use INDEX and MATCH, with ROW()

In Sheet1, cell A2 for example (if your header for that is in cell A1)

=IF(INDEX(Sheet2!$A:$M,ROW(),MATCH(Sheet3!A$1,Sheet2!$A$1:$M$1,0))=0,"",INDEX(Sheet2!$A:$M,ROW(),MATCH(Sheet3!B$1,Sheet2!$A$1:$M$1,0)))

You'll need to adjust the reference but you'll get the idea. You could also use pivot tables for that...

Answer 2

Under each of the 50 headers in Sheet 2 there is an unknown number of rows of data.

I would always prefer formulas over vba however if you have unknown number of rows and 50 headers then personally I will never opt in for formulas. Specially if I have to drag it down. Here is a VBA solution.

Let's say your Sheet2 looks like this

Paste this in Sheet1 worksheet code area.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Target.Cells.CountLarge > 1 Then Exit Sub

    Dim wsI As Worksheet, wsO As Worksheet
    Dim lRow As Long, nCol As Long
    Dim sSrch As String
    Dim aCell As Range, rng As Range

    Set wsI = ThisWorkbook.Sheets("Sheet2")
    Set wsO = ThisWorkbook.Sheets("Sheet1")

    Application.EnableEvents = False

    If Not Intersect(Target, Range("A1:C1")) Is Nothing Then
        sSrch = Cells(1, Target.Column).Value

        Set aCell = wsI.Rows(1).Find(What:=sSrch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            nCol = aCell.Column

            lRow = wsI.Cells(wsI.Rows.Count, nCol).End(xlUp).Row

            Set rng = wsI.Range(wsI.Cells(2, nCol), wsI.Cells(lRow, nCol))
        End If

        If Not rng Is Nothing Then
            Range(Cells(2, Target.Column), Cells(Rows.Count, Target.Column)).ClearContents
            rng.Copy Cells(2, Target.Column)
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Output