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

knis80

New Member
Joined
Aug 11, 2019
Messages
9
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,772
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.
Code:
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

New Member
Joined
Aug 11, 2019
Messages
9
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,772
Try:
Code:
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

New Member
Joined
Aug 11, 2019
Messages
9
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,772
Give this a try:
Code:
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

New Member
Joined
Aug 11, 2019
Messages
9
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,090,453
Messages
5,414,627
Members
403,536
Latest member
JEduardo

This Week's Hot Topics

Top