Sfraizingr
New Member
- Joined
- Oct 19, 2016
- Messages
- 1
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
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