Dynamic Advanced Filter Refreshing

Which is the changing cell on which the event to trigger.

Currently the event will run on each cell change.

Also which cell entry this text refers to *"Giant"*?

May be try this "*Giant*"

I had some success with this. It doesn't work with the client code, so I put in a different filter for B4. It might have issues with the dates too, but it works good on the text columns. I'm too tired to mess with it any more tonight.

Good Luck.

Here's a cool site if you want to learn some good stuff.
Autofilter.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Intersect(Worksheets("Sheet1").Range("B4:M4"), Target) Is Nothing Then Exit Sub

If Target.Address = "$B$4" Then
    With Sheets("Sheet1")
        .Range("B10:M" & .Cells(Rows.Count, 2).End(xlUp).Row) _
        .AutoFilter Field:=Target.Column - 1, Criteria1:=Target.Value
    End With
Exit Sub
End If

Dim FilterCriteria as string
FilterCriteria = "*" & Target.Value & "*"

With Sheets("Sheet1")
    .Range("B10:M" & .Cells(Rows.Count, 2).End(xlUp).Row) _
    .AutoFilter Field:=Target.Column - 1, Criteria1:=FilterCriteria
End With
    
End Sub

you want to auto add the "*" on what you type in?

i tinkered with advanced filters and i could never figure a way to make it work without physically written onto the sheet itself to make range work
if you dont want the criteria to show on sheet itself i would stick to autofilter

as mentioned
numbers/dates work differently

IMO advanced filter should be use when you are replacing destination or eliminating duplicates or requiring a very complex set of criteria

firstly with your clear button
need to insert reset advanced filter code in there as well

Sub Clear()
' Clears the search/input row
    Application.EnableEvents = False
    Range("B4:M4").ClearContents
    Application.EnableEvents = True
    With ActiveSheet
        If .FilterMode And Not .AutoFilterMode Then
            .ShowAllData
        End If
    End With
    
End Sub

worksheet change wont know the difference between data entry and searching so you are going to have conflicts if you want it to search and data entry without both being triggered manually (ie with subroutine via button most likely)

if you are going to trigger it via button then things become slightly less complicated

Thank you for taking the initiative and looking a step further with the clear button! I stuck a .screenupdating = false/true in there to make sure there is no selection happening.

I also edited the sheet and private sub so that this is in there instead

    If Len(Target.Value) > 0 Then
        Application.EnableEvents = False
        Cells(Target.Row + 2, Target.Column) = "*" & Target.Value & "*"
        Application.EnableEvents = True
    Else
        Application.EnableEvents = False
        Cells(Target.Row + 2, Target.Column) = ""
        Application.EnableEvents = True
    End If

This way I have two rows below with the same title (set there) and an updated *text* which gets read instead of the actual search bar. Now I can think about making that submit data!

I don't mind at all having the worksheet change running while inserting the data :D
I fully expect that typing in a brand new entry will leave the filter section blank, and simply add the entry to the last row. Then after pressing clear, all rows will show again with that data included!

The data entry gets a bit complicated, and I avoided using a separate sheet for the data because I wanted to be able to edit the data right then and there. This makes me wonder if I should set the sheet to update when editing the data too.

Still have to think about how to make a filter/sorting ability for client dates :/

I'll post back with some conceptual steps moving forward! Thanks so far!

if you are going to persist with this line of working then i suggest you work with Autofilter like skywriters suggestion instead of advanced filter
you can embed "coding" (adding things to criteria like *) into the filter much easier than advanced filter can

the link Sky provided does have quite a few nifty tips on coding

Originally Posted by RedSummer

Do you think that would help with searching numbers and dates?
It's hard to change when you've given me something so good haha but I know that you are right

With text I think it's not unusual to want to type a partial word and get matches, but with dates you don't usually search by typing a partial date like 11/1, you go for something before or after or between two dates. What are you looking to do with the dates and other numbers? Type a partial number or partial date and have matches come up?

as sky mentioned

Text - no issues
Dates = depends on what you want to enter and what you expect as filter...potentially manageable
i have left it unfinished

Numbers = contains number is actually one of the harder things to replicate, i did a work around using CF and then filter color

anyway you will see what i did