Pivot Table Report Filter – Excel 07

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

32977-Copy-of-PT_1

Reply | Quote

Try this version. data validation should work on this one here 32984-Copy2-of-PT_1

Reply | Quote

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.

Reply | Quote

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.32989-PT_1_v3

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

32995-PT17_DateFormatIssue

Reply | Quote