+ Reply to Thread
Results 1 to 6 of 6

Auto update pivot table filter dates

  1. #1
    Registered User
    Join Date
    11-16-2010
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Auto update pivot table filter dates

    I have several pivot tables that are updated daily with new data.

    Currently, I have filters for "Creation Date" on each of the tables for each month's data. For example, I have a tab for October 2010, November 2010, etc. and on each tab, the pivot tables are filtered by Creation Date for the dates occurring in each month. Each time I add new data, however, I have to update each pivot table to include the new dates, but still exclude the old dates.

    Is there a method for me to avoid having to update the filtered dates each time I add new data?

    Many thanks!

  2. #2
    Valued Forum Contributor SDruley's Avatar
    Join Date
    04-27-2009
    Location
    Conover, NC
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    415

    Smile Re: Auto update pivot table filter dates

    There is a rich selection of vba code to deal with pivot tables. Try doing what you are doing while recording a macro and see what you get.
    Turn Data into Information
    Turn Information into Knowledge
    Turn Knowledge into Direction
    Turn Direction into Leadership
    Turn Leadership into Results
    Stephen Druley

    It's not how quickly you think
    But how deeply you think
    The quality of thinking is measured
    by remoteness to conformance
    Stephen Druley

  3. #3
    Registered User
    Join Date
    11-16-2010
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Auto update pivot table filter dates

    Quote Originally Posted by SDruley View Post
    Try doing what you are doing while recording a macro and see what you get.
    Will the macro be able to check the new dates in a filter?

    I have already edited the pivot tables so that the source automatically includes the new data without needing to edit the source on the individual table. I just want to be able to automatically edit the filters as well.

  4. #4
    Valued Forum Contributor SDruley's Avatar
    Join Date
    04-27-2009
    Location
    Conover, NC
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    415

    Smile Re: Auto update pivot table filter dates

    Refreshing the pivot either programmatically or manually will bring the new dates into the pivot

  5. #5
    Registered User
    Join Date
    11-16-2010
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Auto update pivot table filter dates

    I understand that the dates will be updated in the table, but my goal is that the filter will get updated as well.

    For example, when I updated the tables today, I added new records from 11/13/10, 11/15/10 and 11/16/10. I then had to go to each pivot table and check 11/13/10, 11/15/10 and 11/16/10 in the Creation Date filters for each table so that I still could see all the records that have creation dates in November.

    My goal is that when I add the new records, as soon as I click Refresh, the pivot tables will be completely updated, including the filters.

  6. #6
    Registered User
    Join Date
    11-16-2010
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Auto update pivot table filter dates

    Bumping this thread because I am in desperate need of an answer...

    Edit:
    I found a resolution that I thought may be helpful in case others run into the same trouble I have.

    I had originally added my Creation Date field to the Report Filter of the Pivot Table, which did not allow dynamic updating from one month to another.

    To ensure that the updating was dynamic, I moved the Creation Date field back to the Row Labels and applied a Label Filter directly on the Creation Date field so that the only records shown were for "This Month." Since I did not wish to see the individual dates of each record, I then chose Expand/Collapse and then collapsed the dates to show only the records for the dates I wanted.
    Last edited by kaitco; 11-19-2010 at 02:39 PM. Reason: Resolved on my own

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1