using formulas to sum on various pivot table columns using criteria from other tabs and tables

[Sfraizingr]

Hello,

I have the following formula on one tab of my workbook which presents a client directed view of costs and hours for groups of resources which are described in a pivot table on another tab.

=GETPIVOTDATA("Sum of Tot.Pro.Hrs",'Resource Costs by Phase'!$B$3,"Short_Code",E9,"LU.WP.Code","CL-M")

where

"Sum of Tot.Pro.Hrs" is the name of one of the columns in the pivot table
'Resource Costs by Phase" is the name of the tab in which the pivot table resides
B$3 is the top leftmost cell of the pivot table
"Short_Code" is the column header on the pivot table which correlates the unique resource code which is matched to cells in column E
"LU.WP.Code" is the column header on the pivot table which correlates the unique resource grouping code which is matched to the text "CL-M"


the pivot table has the following column headers

[TABLE="width: 2330"]
<tbody>[TR]
[TD]LU.WP.Code[/TD]
[TD]Short_Code[/TD]
[TD]Sum of Tot.Pro.Hrs[/TD]
[TD]Sum of Tot.Cost[/TD]
[TD]Sum of Tot.Pro.PT.Hrs[/TD]
[TD]Sum of Tot.PT.Cost[/TD]
[TD]Sum of CD0.Pro.Hrs[/TD]
[TD]Sum of CD0.Cost[/TD]
[TD]Sum of CD1.Pro.Hrs[/TD]
[TD]Sum of CD1.Cost[/TD]
[TD]Sum of CD2.Pro.Hrs[/TD]
[TD]Sum of CD2.Cost[/TD]
[TD]Sum of CD3.Pro.Hrs[/TD]
[TD]Sum of CD3.Cost[/TD]
[TD]Sum of CD4.Pro.Hrs[/TD]
[TD]Sum of CD4.Cost[/TD]
[TD]Sum of CD5.Pro.Hrs[/TD]
[TD]Sum of CD5.Cost[/TD]
[TD]Sum of CD6.Pro.Hrs[/TD]
[TD]Sum of CD6.Cost[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]


I am looking for a way to modify the formula so that based on a cell value inputted by the user on another tab altogether, I can have the formula sum on different columns of the pivot table. So for example: if the user inputs values such that the cell reference reads CD-03 the formula would select the values in the column with header "Sum of CD3.Pro.Hrs"

Any help would be greatly appreciated

 

[robsto]

New Member
Join Date
Dec 2015
Location
Keston, Kent
Posts
3
Default Dynamic PowerPivot GETPIVOTDATA Formula Syntax
Hi,


I want to pull in data from a pivot which has month columns, and then paste the formula along without having to manually change the month column. This is how the formula looks:


=+GETPIVOTDATA("[Measures].[Sum of Nov-15]",Sales4Budget!$E$16,"[ALLSALES].[End_Client]","[ALLSALES].[End_Client].&[ALLCLIENTA]").


So I want to change the [Sum of Nov-15] to the pivot column cell reference instead so I can paste the formula. Why is this so difficult?


Welcome to the forum.


What is the cell reference and what exactly is in the cell?
Share
| Like
Rory
Microsoft MVP - Excel


The cell reference and pivot is on another tab. Sales4Budget!$G$16 and has "Sum of Nov-15" in the cell.
Share

In that case the formula should be:


=GETPIVOTDATA("[Measures].["&Sales4Budget!$G$16&"]",Sales4Budget!$E$16,"[ALLSALES].[End_Client]","[ALLSALES].[End_Client].&[ALLCLIENTA]")