Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pivot Table Report Filter - Excel 07

    I have a table of data from a questionaire from which I am trying to create a report showing daily and MTD results. Each Record has a "Questionaire Date" and there's a formula in another column for the corresponding month for MTD.

    The results of the questionaire in the fields are so different that in order to display the results I think I will need to create a pivot table for each field. (Some have Y/N answers other have six or seven choices.)

    Fortunately there are only eleven fields, but that would require changing eleven report filters to report the "Today". Is there a way I can create one cell to which all eleven pivot tables can refer for the report filter?

  2. #2
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I am guessing you would like to have a cell with a drop-down that would control the pivots ?
    It can be done with a little piece of VBA code. Please see below, this one works with three pivot tables but you can multiply the code as many times as you wish (just change pivot table numbers). I don't know what your pivot looks like so you will need to modify the code a little but the idea is that you edit cell A1 and run the macro. that's it. Hope it helps

    Sub Macro1()
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("Measure").CurrentPage = _
    Range("a1").Value
    ActiveSheet.PivotTables("PivotTable2").PivotFields ("Measure").CurrentPage = _
    Range("a1").Value
    ActiveSheet.PivotTables("PivotTable3").PivotFields ("Measure").CurrentPage = _
    Range("a1").Value
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Thats it!

    Thanks jckplck.

    I tried your code in my workbook and got it to work after several tries. Once I finally figured that PivotFields ("Measure") in your code means PivotFields ("Q_Date") in mine it seems to work fine. My Questionaire_Date (Q_Date is my Report Filter) can now be updated in each of the pivot tables.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Well at least I get the Report Filters to update to the same date. But for some reason, the pivot tables aren't updating properly.

    My sample data (attached) has no questionaire date records in February, but when I change the Report Filter to that date I get results other than zero. I checked the pivot table source data range and Refreshed All and then each individually. Calc is set to Auto.

    What can I try next?
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Wow, it took me a while before I realized that your date format is mm/dd/yyyy . Anyway, see if the attached spreadsheet solves your problem. To stop you from entering wrong date into cell A1, I restricted it to only accept dates included in your questionnaire data. You will see that A1 it is now a drop-down but feel free to type date instead, it will throw an error when you enter wrong date. Let me know if this is working for you.

    Copy of PT_1.xlsm

  6. #6
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Try this version. data validation should work on this one here Copy2 of PT_1.xlsm

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I can see the drop-down selections now and the Data Validation range. I selected Enable the Content when presented with a security warning but still can't see the VBA code to execute a macro. In PT_1 the dates in the Filters changes automatically on entry of a new date in A1 but that doesn't happen in this version.

  8. #8
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Had a chat about this code with some clever clogs but it seems that the matter is more complicated than I thought. Try not to filter pivots manually and it should work just right. See v3 of your spreadsheet, hope it has all you need. You will find the code now and will be able to edit it. Will let you know should I find anything that could improve this, otherwise- Good luck.PT_1_v3.xlsm

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    OK. Got the drop-down, I see the Data Validation and the button to execute the macro and the VBA code is now visible. But when I change the date in A1 to Jan 2 the PT2 Count of Staying Here does not update at all, so even though it shows the correct Filter the results are wrong. Next I added a new PT in your latest version (becomes now PT3) and selected MainData and Count of Staying Here as both Row labels and Values and it updated correctly. But, when I added a new line of code for ActiveSheet.PivotTables("PivotTable3") the date in the Filter updates with the macro but the data does not. Even after Refresh and Refresh All. What's up with that?

  10. #10
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    This is proving to be quite a challenge.

    For some reason it does not like date format. Here's how I managed to work around it, follow the steps below and it should work - hopefully
    1. Insert column between Q_date and Staying_here call it lets say Q_date2
    2. insert the following formula in cell D2 =TEXT(C2,"mm/dd/yy") and copy it down
    3. update named range q_date to =QuestionaireData!$D$1:$D$7
    4. refresh PTs
    5. change filter field in all PTs from Q_date to Q_date2
    6. update VBA to refer to Q_date2

    it now should work

    as for extra line of code for the new PT, check first if your new PT is indeed PT3:
    1. right click on PT
    2. PT options
    3. check top of the pop-up window

    hope this helps

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks jckplck. I'll give that a try. I was experimenting with a whole new sheet and stepping through a recorded macro. Thought I'd try ClearAllFilters and PivotCache.Refresh when I noticed that the dates were changing on me. Well not the dates but the formats, anyway. I found that my dates in an individual pivot table had acquired a new format. Where once I had mm/dd/yy I now had m/dd/yyyy. It appears that way in the Report Filter drop-down selection. The new date does not even sort properly in the list (01/11/13, 1/19/2013, 01/18/13, 01/19/13). So it definitely is in the date formatting. I'll give your suggestions a shot and let you know. Thanks for hanging in there with me on this.

    PT17_DateFormatIssue.jpg

Posting Permissions

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