Make dynamic advanced filter trigger on formula value update

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
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
Code:
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 ?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:

Code:
Private Sub Worksheet_Calculate()
    On Error Resume Next
    ActiveSheet.ShowAllData
    Range("A9").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A4").CurrentRegion
End Sub
 
Upvote 0
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.

HCy7dB2

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
HCy7dB2
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Do the formulae in A5:S7 only reference cells in that sheet? or can they reference cells on other sheets?
 
Upvote 0
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.
 
Upvote 0
I want E5 to have the following formula
Code:
=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)
 
Last edited:
Upvote 0
In that case try
Code:
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top