Results 1 to 4 of 4

Refresh Autofilter Upon Cell Value Change

This is a discussion on Refresh Autofilter Upon Cell Value Change within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet with a drop down box that has dates in it in C1. Below that I have ...

  1. #1
    GA3
    GA3 is offline
    New Member
    Join Date
    Nov 2009
    Posts
    31

    Default Refresh Autofilter Upon Cell Value Change

    I have a spreadsheet with a drop down box that has dates in it in C1.

    Below that I have data that I need to filter based on the date in the drop down box occurring between 2 dates listed in the data below it.

    I have already taken care of the date ocurring between the 2 dates by writing a formula that returns a simple "YES/NO" as a result. "YES/NO" is located in column N starting with header in row 4 and data in row 5.

    My issue is getting the list to refilter using autofilter to "YES" whenever the Date in C1 is changed.

    TIA for any help.

  2. #2
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,176

    Default Re: Refresh Autofilter Upon Cell Value Change

    Right click the tab, choose 'view code' and paste the following:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$1" Then
            Range("n4").CurrentRegion.AutoFilter Field:=14, Criteria1:="yes"
        End If
    End Sub
    HTH
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  3. #3
    GA3
    GA3 is offline
    New Member
    Join Date
    Nov 2009
    Posts
    31

    Thumbs up Re: Refresh Autofilter Upon Cell Value Change

    That works perfectly! THANK YOU!

  4. #4
    GA3
    GA3 is offline
    New Member
    Join Date
    Nov 2009
    Posts
    31

    Default Re: Refresh Autofilter Upon Cell Value Change

    Ok... Let's fast forward 3 years... I have been requested to update this to include some additional data. I now have a Category column in column z. I have created a drop down with data with all the categories involved in cell C2.

    This code is attached to this sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    End If

    If Target.Address = "$C$1" Then
    Range("n4").CurrentRegion.AutoFilter Field:=14, Criteria1:="yes"
    End If

    End Sub

    This gives me what I needed originally...when I change the date in C1 it refilters the sheet to reflect things that occur during the date's time frame.

    I now need it to refilter based on date AND THEN the category chosen, and I need it to do it everytime the category is changed.

    If the date is changed then it should reset the entire sheet.

    Can anyone please help me?

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
  •  


DMCA.com