Aufilter by contents in another cell

[shakeregg]

Hey all,

Hopefully a quick one for you guys.

I am looking to auto filter G6:G500 depending on the contents in G4.

For example, if G4 contains 'Fred' all the G7:G500 cells containing 'Fred' are filtered and become visible. If G4 changed to 'Dave' all the cells in G7:G500 which contain 'Dave' will be filtered and become visible. G6 contains the header 'Names'

Cheers in advance all if anyone can help.

 

[Joe4]

Have you looked at Advanced Filters?
With those, you set the condition of your Filter in other cells.
See here: https://www.contextures.com/xladvfilter01.html

 

[shakeregg]

Cheers for replying!

Yep tried that but unfortunately it's not filtering automatically when G4 changes

 

[Joe4]

You can add some VBA to make that happen automatically.
See: https://www.extendoffice.com/documents/excel/4592-excel-update-advanced-filter-automatically.html

 

[BENNASR]

if A1 = "NAME" and in A2 y'll put the name
the table is in A5 to A11 and in A5 also ="NAME" in A6=dave/A7=john ....
you ca use this :

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A5:C11").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:A2"), Unique:=False
End Sub