Make dynamic advanced filter trigger on formula value update

[szakharov7723]

So currently I have an advanced filter that triggers on value input. However when I try to make criteria cell formula based it ignores changes. unless I click on cell and hit enter
Here is my advanced filter

Private Sub Worksheet_Change(ByVal Target As Range)    If Not Intersect(Target, Range("A5:S7")) Is Nothing Then
        On Error Resume Next
        ActiveSheet.ShowAllData
        Range("A9").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A4").CurrentRegion
    End If
End Sub

Is it possible to make triggered on formula value changes ?

 

[DanteAmor]

If in the cell you have a formula, what is changing is the result of the formula, you are not changing the value of the cell, since the formula in the cell remains the same.

One option is to put in the range the cells that make up the formula, that is, if the cell A5 has the formula =A3 and really the value you are changing is A3, then you should have:
If Not Intersect (Target, Range ("A3"))

Another option is the Calculate event, try:

Private Sub Worksheet_Calculate()
    On Error Resume Next
    ActiveSheet.ShowAllData
    Range("A9").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A4").CurrentRegion
End Sub

 

[szakharov7723]

Thank you for clarifying this.
I am trying to figure out how to apply 1st method, as the 2nd one doesn't work, because I need to use value change as well.

So far the way it works. I input the value into filter (above) and data gets sorted out (below). I would still like other fields to be triggered by value change. However I am not sure how to make Problem and Work Performed fields an exception that triggers on calculation change. Can you please help me to figure this out.

https://ibb.co/HCy7dB2

 

[DanteAmor]

In this case, an image does not tell me much, I need to see the formulas and which are the references that each formula has.

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[szakharov7723]

Hey, thank you for helping.
Is it possible to discuss it without uploading the file ? From what I read dropbox service has a weird policy, which I do not support.
Formula is very simplistic, it just refers to other cell outside of filter.

 

[Fluff]

Do the formulae in A5:S7 only reference cells in that sheet? or can they reference cells on other sheets?

 

[DanteAmor]

Hey, thank you for helping.
Is it possible to discuss it without uploading the file ? From what I read dropbox service has a weird policy, which I do not support.
Formula is very simplistic, it just refers to other cell outside of filter.

Then put your formula here, it also describes what data you update on the sheet to know when the change event should be applied.

 

[szakharov7723]

I want E5 to have the following formula

=Concatenate("*",P2,"*")

Where E5 is a part of filter and P2 is just a cell in the same sheet.
(this will allow to search based on keyword and apply different analysis to that word)

 

[Fluff]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A5:S7,P2")) Is Nothing Then
        On Error Resume Next
        ActiveSheet.ShowAllData
        Range("A9").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, criteriarange:=Range("A4").CurrentRegion
    End If
End Sub

 

[szakharov7723]

So far I am planning to reference cells in the same sheet.
And formula will be only in E5 and F5 cells, other cells will still be change on value.