Forum Discussion

Angela2021's avatar
Angela2021
Copper Contributor
Apr 18, 2021

Extracting data using a Dropdown list that will auto generate in another sheet.

I am very new to excel so excuse my lack of knowledge. I am trying to get information in a row to auto populate in another sheet when I select an item from a dropdown list. So, if I choose "dual" in F2 (sheet1), I would like the date (A2), name (B2) and age (C2) auto populate in sheet 2 the date (A2), name (B2) and age (C2).

 

Is this possible? If so, how can I accomplish this. I tried to screen shot but was unable to.

 
 

 

 

 

 

 

 

excel

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 19, 2021

    Angela2021 

    Right-click the sheet tab of the first sheet.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    Dim wsh As Worksheet
    If Not Intersect(Range("F:F"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set wsh = Worksheets("DUAl SEARCHES")
        r = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In Intersect(Range("F:F"), Target)
            If rng.Value = "Dual" Then
                r = r + 1
                wsh.Range("A" & r).Resize(1, 3).Value = Range("A" & rng.Row).Resize(1, 3).Value
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook.

    Make sure that you allow macros when you open it.

     

    I have attached a version with the code.

    • Angela2021's avatar
      Angela2021
      Copper Contributor
      Apr 19, 2021
      I was looking at the code and in the other workbook I am working on in the "DUAL" I made it a hyper link. On the one you worked on the "dual" drop down is not a hyperlink. I was think this as the reason it was not working for me.
    • Angela2021's avatar
      Angela2021
      Copper Contributor
      Apr 19, 2021
      Thank you so much!
      The one you created works like a charm. When I try to recreate it it doesn't work. The only difference I can see is that the "DUAL" in my other worksheet is a hyperlink. Not sure if this makes a difference. Would I be able to use this code for the shelter drop down in the same row along with the dual for the shelter tab? Would I just need to past the code under the first one and change the language to "SHELTER" instead?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 19, 2021

        Angela2021 

        Not sure what you mean by "The only difference I can see is that the "DUAL" in my other worksheet is a hyperlink."

        To accommodate SHELTER too, the code could look like this:

        Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    Dim wsh As Worksheet
    If Not Intersect(Range("F:F"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set wsh = Worksheets("DUAl SEARCHES")
        r = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In Intersect(Range("F:F"), Target)
            If rng.Value = "Dual" Then
                r = r + 1
                wsh.Range("A" & r).Resize(1, 3).Value = Range("A" & rng.Row).Resize(1, 3).Value
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
    If Not Intersect(Range("H:H"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set wsh = Worksheets("SHELTER LOG")
        r = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In Intersect(Range("H:H"), Target)
            If rng.Value = "SHELTER" Then
                r = r + 1
                wsh.Range("A" & r).Resize(1, 3).Value = Range("A" & rng.Row).Resize(1, 3).Value
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Resources