K
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
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