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

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