none
Display rows from another sheet on dropdown list selection in excel 2016 RRS feed

  • Question

  • Hi,

    I have one excel worksheet contains set of information as below-

    ID	Country	Name	Email
    1001	India	A	Email A
    1002	India	A	Email A
    1003	China	B	Email B
    1004	China	C	Email C
    1005	Nepal	D	Email D
    1006	Nepal	D	Email D
    1007	India	E	Email E
    

    IN another sheet I have one dropdown list for Countries. If I select a country like India from the dropdown list, then all rows for India will show under the list as below-

    Select Country	India		
    ID	Country	Name	Email
    1001	India	A	Email A
    1002	India	A	Email A
    1007	India	E	Email E
    

    If I select China then existing rows will be replaced by China rows. User can edit rows information after selection.

    Could anyone help me please?

    Friday, October 12, 2018 10:31 AM

All replies

  • Hi pointtoshare,

    You could refer the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$G$13" Then
            'You could find related row by the value, and match all rows to another worksheet. 
        End If
    End Sub

    If you edit the rows, simply write back the edited information to the worksheet.

    Best Regards,
    Bruce

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, October 15, 2018 9:43 AM
    Moderator
  • Hi Bruce,

    Thanks for your response.

    I don't need to edit the source information where rows are being copied, the source file will be unchanged.

    Could you please provide some 

    'You could find related row by the value, and match all rows to another worksheet. 

    Could you please help me on the above?

    Thanks.


    • Edited by pointtoshare Wednesday, October 17, 2018 3:36 AM
    Monday, October 15, 2018 11:46 AM
  • Hi pointtoshare,

    Please refer to the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
       Dim row As Integer 
       Dim row2 As Integer
       row = Worksheets(1).range("A65536").end(xlup).row()
       row2 = 3
       Worksheets("2").Range("a3").CurrentRegion.ClearContents
       For i = 2 to row
         if Range("B" & i).Value = Target.Value Then
           Worksheets(2).Range("A" & row2) = Worksheets(1).Range("A" &i)
           Worksheets(2).Range("B" & row2) = Worksheets(1).Range("B" &i)
           Worksheets(2).Range("C" & row2) = Worksheets(1).Range("C" &i)
           Worksheets(2).Range("D" & row2) = Worksheets(1).Range("D" &i)
           row2 = row2 + 1
         end if
       Next i
    End Sub

    Hopefully it helps you.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, October 17, 2018 11:15 AM
    Moderator