filter on sheet1 also filters sheet2 based on sheet1 filtered values

[rjvr]

Hello,
I have multiple sheets in a excel file and the Address sheet contains the columns: Postal Code, House, City and Employee sheet contains the columns: Name, Contact, House, Postal Code. I wish to filter my sheets so that I can find the employees from a particular City.

Address Sheet:

Postal Code	House	City
EC1V 9LT	372 Old Street	London
NW3 6BT	176 Finchley Road	London
M14 5TD	6 Wilmslow Road, Rusholme	Manchester
YO1 9RA	7-9 Clifford Street	York

Employee Sheet:

Name	Contact	House	Postal Code
Employee 1	XXXXX-XXXXXX	372 Old Street	EC1V 9LT
Employee 2	XXXXX-XXXXXX	176 Finchley Road	NW3 6BT
Employee 3	XXXXX-XXXXXX	6 Wilmslow Road, Rusholme	M14 5TD
Employee 4	XXXXX-XXXXXX	7-9 Clifford Street	YO1 9RA

Filtered Address Sheet:

Postal Code	House	City
EC1V 9LT	372 Old Street	London
NW3 6BT	176 Finchley Road	London

Which will automatically filter the Employee Sheet also giving the below result:

Name	Contact	House	Postal Code
Employee 1	XXXXX-XXXXXX	372 Old Street	EC1V 9LT
Employee 2	XXXXX-XXXXXX	176 Finchley Road	NW3 6BT

Thank you for the help.

 

[BigBeachBananas]

Hello,
I have multiple sheets in a excel file and the Address sheet contains the columns: Postal Code, House, City and Employee sheet contains the columns: Name, Contact, House, Postal Code. I wish to filter my sheets so that I can find the employees from a particular City.

Address Sheet:

Postal Code	House	City
EC1V 9LT	372 Old Street	London
NW3 6BT	176 Finchley Road	London
M14 5TD	6 Wilmslow Road, Rusholme	Manchester
YO1 9RA	7-9 Clifford Street	York

Employee Sheet:

Name	Contact	House	Postal Code
Employee 1	XXXXX-XXXXXX	372 Old Street	EC1V 9LT
Employee 2	XXXXX-XXXXXX	176 Finchley Road	NW3 6BT
Employee 3	XXXXX-XXXXXX	6 Wilmslow Road, Rusholme	M14 5TD
Employee 4	XXXXX-XXXXXX	7-9 Clifford Street	YO1 9RA

Filtered Address Sheet:

Postal Code	House	City
EC1V 9LT	372 Old Street	London
NW3 6BT	176 Finchley Road	London

Which will automatically filter the Employee Sheet also giving the below result:

Name	Contact	House	Postal Code
Employee 1	XXXXX-XXXXXX	372 Old Street	EC1V 9LT
Employee 2	XXXXX-XXXXXX	176 Finchley Road	NW3 6BT

Thank you for the help.

 

[BigBeachBananas]

The easiest way to do this is to use VLOOKUP to join your 2 sheets together.
First: add a new column on both sheets and create a unique join key by =CONCAT("House", "Postal Code"). However, on your "Adress" Sheet, make sure this column is on the left of the City column
Second: On your "Employee sheet", in an empty column use VLOOKUP("Join Key", 'Adress Sheet'$JoinKey:$City, 2, FALSE)
With this, you'll have all of the employees' info on the Employee Sheet and do not need the Filter sheet and can simply filter by City on the Employee Sheet to find all employees in the desired city.

 

[rjvr]

Thank you for your answer, but is it possible to do it without adding columns to the Employee sheet?

 

[BigBeachBananas]

Thank you for your answer, but is it possible to do it without adding columns to the Employee sheet?

Thank you for your answer, but is it possible to do it without adding columns to the Employee sheet?

There’s a couple approaches:
1) Use the join key to do either index/match or vlookup the name, contacts from Employee sheet on to your Address sheet. Same result but the columns will be added to your Adress sheet instead of Employee sheet. However, you’ll still need to make a join key column on your Employee sheet.
2) make a copy of your employee sheet and follow the same procedure mentioned before. This way, you’ll preserve the original Employee sheet

 

[kevin9999]

If you were looking for a VBA solution, then the following will do what you want. It assumes your headers on both sheets are in row 1 and that your data starts in column A. Firstly, put the following code in a standard module:

Option Explicit
Sub CopyFilter() 
    Dim arr, lr As Long, i As Long, ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("Address")
    Set ws2 = Sheets("Employee")
    lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    
    arr = ws1.Range("A2:A" & lr).SpecialCells(xlCellTypeVisible)
    arr = Application.Transpose(Application.Index(arr, 0, 1))
    
    For i = LBound(arr) To UBound(arr)
       arr(i) = CStr(arr(i))
    Next i
    
    With ws2.Cells(1, 1).CurrentRegion
       .AutoFilter 4, Array(arr), 7
    End With
End Sub

Secondly, you said you wanted this process to happen "automatically". That being the case, start by putting the following formula into a spare cell/column on the Address sheet:

=COUNTA(A:A)

Then, put the following code in the worksheet change code area of the Address sheet:

Private Sub Worksheet_Calculate()
    CopyFilter
End Sub

Now, if you change the filter on the Address sheet (column A) - the same filter will be applied to column D on the Employee sheet.

Good luck!