+ Reply to Thread
Results 1 to 4 of 4

Dynamic Filter For Pivot Table Date

  1. #1
    Registered User
    Join Date
    09-23-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Dynamic Filter For Pivot Table Date

    Hi All,

    Sample data: sample.xlsx

    So here is what I am hoping to do.. I am looking to create a bar chart that will show the average number of "Total Leads" per "Type" to date. I want to make sure to filter out future dates so that the average is not thrown off. Normally, I would set up a filter and select the dates I need to run, but -- 1) this same thing needs to be done for last year, so after I can build this table, I will do the same for the 2014 file and create a consolidated table from which I will create a bar chart; 2) those who will be taking on this report are less excel savvy than I am and if I can create some thing that does most of the work for them that would be great!

    In essence when the team wants to run the report, they would refresh the pivot tables to update the "dynamic" date, then the consolidated table (which would just link, to one place, the data I need from each individual table) would update and so would the bar chart.

    I think this might work with the slicer, since I believe you can add macros to slicers. I have no idea if this is the case, or (if it is) how to do it.

    I tried to add a label filter using "less than: Today()" but the Today() did not work in the field.


    Let me know if you need more information, or if this is not possible. Any help would be greatly appreciated!


    Thanks
    Art


    p.s. given the level of depth this small (and not too technological team) has been asked to review their "events" based on leads per type, per week, per year, against forecast and against last year.. I told them that they need a full system and not excel. so if anyone can recommend a better tool to easily create reports, that would be great too!

  2. #2
    Registered User
    Join Date
    09-23-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Dynamic Filter For Pivot Table Date

    So I was able to combine a few macros to get this

    Please Login or Register  to view this content.
    It works but I get an runtime error 13 -- type mismatch

    The debug highlights the "If" line.. any thoughts as to why?

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Dynamic Filter For Pivot Table Date

    i added the slicer to the file you provided and run the macro

    unable to replicate the type mismatch error you had

    this section
    Please Login or Register  to view this content.
    ...not sure what this is meant to achieve
    as todaystring is not used in the code?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Dynamic Filter For Pivot Table Date

    Please Login or Register  to view this content.
    you dont really need the else as well as the principle of the code is to select (unfilter) everything and just unselect pivot items which are greater than today
    Last edited by humdingaling; 12-10-2015 at 07:11 PM. Reason: typo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to filter a pivot table in vba (Dynamic)
    By fluffyvampirekitten in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2015, 05:24 AM
  2. Pivot table dynamic range too large, unable to filter dates.
    By Si902 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-22-2015, 03:28 AM
  3. Dynamic filter on pivot table?
    By Cleland in forum Excel General
    Replies: 0
    Last Post: 10-23-2014, 07:50 AM
  4. Pivot Table: OR condition filter on Multi Date/Sum Filter
    By ddalt10 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-09-2013, 12:49 PM
  5. [SOLVED] How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?
    By Rhino_dance in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-28-2013, 11:01 AM
  6. Date filter on Pivot Table?
    By umss in forum Excel General
    Replies: 1
    Last Post: 03-27-2012, 03:33 AM
  7. Pivot Table Date Filter
    By coolzero in forum Excel General
    Replies: 1
    Last Post: 07-09-2010, 04:11 PM

Tags for this Thread

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