PDA

View Full Version : Multiple Row Totals Based On Pivot Table



krysolov
01-23-2012, 03:09 PM
I have a pivot table showing production report by year/month (columns) of each production line/item (rows). My issue is that I need to have multiple columns of totals, such as: sums of all periods (grand total), last 6 periods, last quarter; averages of all periods, last 6 periods, last quarter; max of all periods, 6 periods, quarter... and then a few more. The number of rows may change, but the number of columns is static.

Since formulas outside of the pivot table would not expand/contract with it, I thought the best way would be have a macro executed on refresh event (well, I have a button that triggers the refresh of data, then pivot table) to clear out old totals, then recalculate them for each row.

I tried starting "simple" by spinning through all PivotItems associated with the "Item No." PivotField, determining which actual row that item is located on, then using hard-coded offsets calculate the grand total. I couldn't find an "easy" way to determine an address of a cell where a particular PivotItem resided, so I used the Find method to get the row number of the cell and go from there, but it's extremely cumbersome and involves a lot of hardcoded offsets. I'm hoping there is a more elegant way to accomplish this!

Thank you for any help!

Sergey