Excel Filter based on cell

[natheplas]

Hi Forum,

I have a table containing data A2:K173, there could be more rows added so it could grow to A2:K400. Row 2 is the table headers.

What I want to achieve is in Cell B1, someone can write a number or product code and then the table filters by that product code.

All the product codes are in Column D (D3:D175).

I've googled it for a while now and I can't get anything, others have done it, but I cant replicate it.

It's essentially filter column D, on work sheet 'Ordering Info' by cell value B1.

Could we do it so if nothings in there it doesn't filter, but if there is a value typed it filters.

Thank you. It's really appreciated.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Address(0, 0) = "B1" Then Exit Sub
   If Me.AutoFilterMode Then Me.AutoFilterMode = False
   If Target.Value <> "" Then
      Range("A2").AutoFilter 4, Target.Value
   End If
End Sub

This needs to go in the sheet module

 

[Eric W]

Use Fluff's code if you want an automatic solution, but you may want to look into Advanced Filter:

https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/