Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycling Through Pivot Field
I want to create a macro that cycles through a Year-Month field in a pivot
table, allowing me to easily update a chart for different years and months. In other words, I'd like to be able to click an up or down button to update a pivot table and chart to show a set of data for different year-months. I'm also considering just a single "start" button to cycle through all year-months at one time. Can anyone offer suggestions on how to do this? Thanks, Todd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycling Through Pivot Field
Hi Todd,
There's a better way. I've struck this problem regularly. The Pivot table will provide you with a categorised datatable, so what you need is a way to get the data out of it on a rolling basis. Here's what I do. I have three sheets: (1) The Base Data, (2) the Pivot Table and (3) the Charting Table Assign a cell (A1) on the Charting Table for the "Reporting Month" or "Week Ending" or what ever your reporting period is. Create the charting table so that a simple list of dates is set-up one under another, and all relative to the one above ie: B3 = A1 B4 = B3+7 B5 = B4+7 B6 = B5+7 etc.. Then the C3 Cell looks into the Pivot Table to extract the Value for date value in B3. The =GETPIVOTDATA() formula looks complex, but you can bypass it's complexity by simply keying in '=' in C3 then going to the pivot table and selecting the value you want to extract. Once you've got it, change the formula so that it looking in to B3 for it's date. Then copy this formula down the charting data. Viola! You've created a rolling report (and chart) that automatically updates itself based on the value of A1. To protect against errors (like #REF! errors) in the GETPIVOTDATA formula, I use: =IF(ISERROR(GETPIVOTDATA("Hours",'PVT-ITIL Breakdown'!$A$3,"Group",B $3,"ProductName",B$24,"Activity",C3)),"",GETPIVOTD ATA("Hours",'PVT- ITIL Breakdown'!$A$3,"Group",B$3,"ProductName",B$24,"Ac tivity",C3)) - But there may be a nicer way than this. Hope this is not too confusing. Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove a Calculated Field from the Pivot Table field list | Excel Discussion (Misc queries) | |||
How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
Unable to change field settings in calculated field in a pivot tab | Excel Discussion (Misc queries) | |||
pivot tables: changing 'sum of' to 'average of' & cycling through pages | Excel Programming | |||
Pivot Table Cycling Through Page Fields Automatically | Excel Discussion (Misc queries) |