Summing data in a Pivot table
I have a pivot table with columns as months, rows as product codes, and the
body of the table in $ amounts. I want to sum a range of month, eg: Jun 06 to May 07. How can I do this? I don't want to use absolute column references as month change location as more/less data is added to the pivot table. Getpivotdata doesn't seem easy to nest in another formulas. Any suggestions? Many thanks -- Peter London, UK |
Summing data in a Pivot table
Hi Peter
The GetPivotData function, is not the easiest to deal with and doesn't lend itself to Offset etc. I would set up a table on another sheet. In B1, enter the date for the first month of interest, e.g 01 Jun 2006 and custom format as mmm In C1 enter =DATE(YEAR(B1),MONTH(B1)+1,1) again formatted as mmm Copy C1 across through D1:M1 In A2 and onward enter the rows names you are interested in from the PT In B2 enter your GetPIvotData formula, referencing B$1 and $A2 and copy as appropriate. Do the summing from the rows of data created in this table. Changing the date in B1, will alter the range of 12 months that you pick up. -- Regards Roger Govier "Peter" wrote in message ... I have a pivot table with columns as months, rows as product codes, and the body of the table in $ amounts. I want to sum a range of month, eg: Jun 06 to May 07. How can I do this? I don't want to use absolute column references as month change location as more/less data is added to the pivot table. Getpivotdata doesn't seem easy to nest in another formulas. Any suggestions? Many thanks -- Peter London, UK |
All times are GMT +1. The time now is 12:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com