Help needed to get whole column data as result by choosing a drop-down value in other sheet!

[knis80]

Hi All I am finding it hard to get results in the following scenario:

I have three worksheets Sheet 1, Sheet 2 and Sheet 3

In Sheet 1
I have a drop-down with values i.e. Team 1, Team 2, Team, Team 3 and Team 4.

In Sheet 2
I have 4 Columns with the headings Team 1, Team 2, Team, Team 3, Team 4. Each Column have 40 names of the participants.

In Sheet 3
I have one column "Selected team".

I am finding a way by which I may be able to select the team from the drop-down list in Sheet one and get the whole list of the participants mentioned in the respective column of sheet 2 as the result in sheet 3.

i.e. If I choose Team 1 from the drop-down I wish to get all the names mentioned in the column Team 1 of sheet 2) in sheet 3's Selected Team column and if I choose Team 2 from the drop-down I wish to get all the names mentioned in the column Team 2 of sheet 2) in sheet 3's Selected Team column.

what technique I have to adapt to get the desired result. Please help!

Regards

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. I assumed that your drop down in Sheet1 is in cell A1. Change that cell in the code (in red) to suit your needs. Close the code window to return to your sheet. Make a selection in your drop down list. I also assumed you have headers in row 1 and the data starts in row 2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("[COLOR="#FF0000"]A1[/COLOR]")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, fnd As Range, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet3")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set fnd = srcWS.Rows(1).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        With srcWS
            .Range(.Cells(2, fnd.Column), .Cells(LastRow, fnd.Column)).Copy desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[knis80]

Hi mumps!

Thank you very much for the code it works perfect like a magic.

However, I wish if after selecting the different value from the drop-down the existing data (which was generated by the previous selection attempt) may be replaced with the new list originating from the recent selection will make this code extremely useful to me!

So I request you to please assist me in this regard also!

Thank you very much for the great help!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, fnd As Range, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet3")
    desWS.UsedRange.Offset(1, 0).ClearContents
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set fnd = srcWS.Rows(1).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        With srcWS
            .Range(.Cells(2, fnd.Column), .Cells(LastRow, fnd.Column)).Copy desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With
    End If
    Application.ScreenUpdating = True
End Sub

 

[knis80]

Thank you very much for such a quick response and kind help! the code you've shared works exactly what was desired by me, This code is great if I put some text values and chose Option1 from the drop down list in sheet1 the code displays all the content of column "A" and if I choose Option 2 or 3 It displays the data from column B and C.

However when I tried formula as cell content like getting value from another worksheet's cell i.e. "=Sheet4!A2" or "=Sheet4!B2" or "=Sheet4!C2" it always shows the formulas/contents of column "A" even when I choose any other option of the drop-down options list. is there any way to correct this issue?

 

[mumps]

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long, fnd As Range, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet3")
    desWS.UsedRange.Offset(1, 0).ClearContents
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set fnd = srcWS.Rows(1).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        With srcWS
            .Range(.Cells(2, fnd.Column), .Cells(LastRow, fnd.Column)).Copy
            desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End With
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[knis80]

Hi! Thank you for providing me the resolution of my issue! This was my second query asked on MrExcel and both of the time I was given a perfect solution of my issue Thank you mumps with your help I am now able to save my time which was spent on this task every time! Your Code(s) worked exactly as required by me. Regards,

 

[mumps]

You are very welcome.