Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Make dynamic advanced filter trigger on formula value update

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Make dynamic advanced filter trigger on formula value update

    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 ?

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    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
    Last edited by szakharov7723; Apr 2nd, 2019 at 01:02 PM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    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.
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    Do the formulae in A5:S7 only reference cells in that sheet? or can they reference cells on other sheets?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    Quote Originally Posted by szakharov7723 View Post
    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.
    Regards Dante Amor

  8. #8
    New Member
    Join Date
    Jun 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    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 by szakharov7723; Apr 8th, 2019 at 11:14 AM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Jun 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make dynamic advanced filter trigger on formula value update

    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 by szakharov7723; Apr 8th, 2019 at 11:18 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •