Filter worksheet 2 data based on worksheet 1 in same book

Question

I'm trying to filter worksheet 2 data based on worksheet 1 data in same workbook(MS Excel). Here is my requirement, I have data in first worksheet which contains 100 records and it has employee Id as a key but this sheet will contain data related to employee personal information such as name, age. However, second sheet will contain more information of that particular employee such as experience details. So, I would like to provide user a functionality that user clicks on employee ID then it will redirect to another sheet with filtered data.

Below is the sample data which will be available in two different sheets in same workbook:

Sheet 1:

Name | Employee ID | Age | Sex |

ABC | 123 | 23 | M |
BCD | 234 | 25 | F |

Sheet 2:

Employee ID | Previous Company | Exp

123 | Ace | 4
123 | Tech | 5
234 | Ace | 4

If I clicks on 123 in sheet 1 then it should redirects me to sheet 2 and filters the 123 records only.

If anyone knows the answer then please reply soon as I need to build this functionality by Java code. But, before starting the code I should know whether it is possible by excel or not.

Do you want to just select a id from dropdown then it will show automatically all data relative to that id from sheet2? — Harun24HR
No, I want to filter the data in sheet2 when user clicks on employee Id value's cell. For instance, if user clicks on cell which has employee Id 123 in sheet1 then it will redirect him to sheet2 with filtered data by employee Id 123. — Ravi Nain

Harun24HR Harun24HR · Accepted Answer · 2016-01-23T14:02:05

I am assuming your Sheet1 data starts from A1 cell and Employee ID is in column b I also assume that sheet2 data starts from cell A1 and Employee ID is in Column A.

Use the following sub to filter data of sheet2 based on sheet1 employee id. Use the codes in sheet1 Worksheet_Change event not in Sheet2.

How to use: You have to double click in Sheet1 Employee ID cells then press Enter or Tab key.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastUsedRow As Long
Dim FilterCriteria As Variant

    If Not Application.Intersect(Target, Range("B1:B10")) Is Nothing Then
        FilterCriteria = Target.Value
          LastUsedRow = Sheets("Sheet2").Range("A1").End(xlDown).Row
         Sheets("Sheet2").Range("A1:C" & LastUsedRow).AutoFilter Field:=1, Criteria1:=FilterCriteria
        Sheets("Sheet2").Select
    End If

End Sub

For more clarification see the below screenshot.

Filter worksheet 2 data based on worksheet 1 in same book

Name | Employee ID | Age | Sex |

Employee ID | Previous Company | Exp

1 Answers