Excel formula or VBA to paste list of data from reference worksheet if specific text is selected in data worksheet

[Lisa Harris]

Hi, hoping someone can help as I cannot find a solution at all.

I have a workbook with two sheets. One is the active data sheet (sheet 1), the other is a reference / list (sheet 2).

Sheet 2 details across the columns (C:E) the 'Types', and below each type, lists the Tasks for that type (used letters for this example):


[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, align: center"]1[/TD]
[TD="width: 64, align: center"]2[/TD]
[TD="width: 64, align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]a[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]a[/TD]
[/TR]
[TR]
[TD="align: center"]b[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[/TR]
[TR]
[TD="align: center"]c[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]e[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl73"][/TD]
[/TR]
</tbody>[/TABLE]



Sheet 1 is the data entry sheet. In column F, a 'type' will be selected from a drop down list. When this happens, I need the relevant list for that type on sheet 2, to be pasted in column H on the row of the 'Type' and subsequent rows needed to fit the list. Once this has been done, I would also like the Type cell (column F) to be merged and centered to reach the number of rows the list is, and the same for the title (column G) - please see below (unable to show the merged cells as it sent my table all off - apologies)



[TABLE="class: grid, width: 553"]
<tbody>[TR]
[TD="align: center"]column[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Title[/TD]
[TD="align: center"]Tasks[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]title 1[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]d[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]e[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]title 2[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]d[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]title 3[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]d[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]title 4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]d[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]e[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]title 5[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]d[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]e[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Many thanks in advance for any help you can offer

Lisa

 

[mumps]

Sorry... misposted

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. First insert the title in column G and then make a selection in column F.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Set foundVal = Sheets("Sheet2").Range("C1:E1").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Sheet2").Range(Sheets("Sheet2").Cells(2, foundVal.Column), Sheets("Sheet2").Cells(6, foundVal.Column)).Copy _
            Cells(Rows.Count, "H").End(xlUp).Offset(1, 0)
        With Range("F" & Target.Row & ":F" & ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MergeCells = True
        End With
        With Range("G" & Target.Row & ":G" & ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MergeCells = True
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[Lisa Harris]

That is fantatsic - Thank you so much! Worked straight away.

Huge thank you. Lisa

 

[mumps]

You are very welcome.