GetPivotData Reference Range

[Kevin Labore]

Hi

I am trying to use GetPivotdata to sum the data in a range
The current pivotable displays the data with the weeks as Cols, and
Department/Year as Rows

I can use the following forumla to get the sales for a particular
Week/Dept/year:

=GETPIVOTDATA("DeptSales",DeptSales_Act!$A$3,"Year",$A$5,"Week",$C$4,"DeptDesc",$B26)

What I would like to do is get the last 4 weeks(as well as 8 weeks and 12
weeks) so I can compare/indentify trends

For the 4weeks the reference cols would be C:F
the 8weeks the reference cols would be C:J
the 12weeks the reference cols would be C:N

Presently to sum last 4 weeks I use a formula like this (not very
practical):

=GETPIVOTDATA("DeptSales",DeptSales_Act!$A$3,"Year",$A$5,"Week",$C$4,"DeptDesc",$B26)+GETPIVOTDATA("DeptSales",DeptSales_Act!$A$3,"Year",$A$5,"Week",$C$4-1,"DeptDesc",$B26)+GETPIVOTDATA("DeptSales",DeptSales_Act!$A$3,"Year",$A$5,"Week",$C$4-2,"DeptDesc",$B26)+GETPIVOTDATA("DeptSales",DeptSales_Act!$A$3,"Year",$A$5,"Week",$C$4-3,"DeptDesc",$B26)

is there a way reference cells this way(sum range?) or do I need to do a
workaround?

thanks

Kevin

 

[Debra Dalgleish]

You could create copies of the pivot table, and group the week by 4s, 8s
and 12s.
Each week, manually or programmatically change the start number of the
grouping, to get the ranges that you need.
Then, use GetPivotData formulas to pull results from the grouped data.

 

[Kevin Labore]

Hi Debra

thanks for the suggestion -- that is a work around I had thought of.
Not a prefferable way to do what I would like to accomplish.
I will try an sum array formula referencing the week # ($c$4)

thanks again for the help

Kevin