Grand total in seperate cell for Pivot table

[christhedonstar]

Hi,

How do I get the "grand total" from a pivot table into cell B2 (for example).

When I do = to the cell Excel automatically creates the formula

=GETPIVOTDATA("Stuff",$Z$12)

But when I change the pivot table then I lost the total. I want to get the total of the data the pivot table uses. The reason is so that the user can check the results of the pivot table against some of the raw data. He doesn't trust the pivot table.

Thanks,

Chris

 

[christhedonstar]

Yup calculation is always manual. Still should update on f9 though.

 

[combo]

It is still not clear for me how flexible testing you expect. Moreover, in my case a reference to other cell as the first argument in the GETPIVOTDATA function does not work, only pure string is accepted.
I would prefer to extend functionality of this function with VBA (below). CellInTable helps to refer to pivot table, similarly to $Z$12. The first function accepts raw field names and returns summary data (if in table). The second refers to data field header and returns calculation method (useful when default name was replaced by custom one. As VBA offers quite a lot programming of pivot table, more data can be extracted in this way, anyway it will always be extraction from the report, not from the raw data.

Public Function vbaGetPivotData(CellInTable As Range, FieldName As String)
vbaGetPivotData = CellInTable.PivotTable.GetPivotData(FieldName).Value
End Function

Public Function CalcType(CellInTable As Range, FieldName As String)
Dim ConsolidationFunction As XlConsolidationFunction
ConsolidationFunction = CellInTable.PivotTable.PivotFields(FieldName).Function
Select Case ConsolidationFunction
Case xlAverage
    CalcType = "Average"
Case xlCount
    CalcType = "Count"
Case xlCountNums
    CalcType = "CountNums"
Case xlMax
    CalcType = "Max"
Case xlMin
    CalcType = "Min"
Case xlProduct
    CalcType = "Product"
Case xlStDev
    CalcType = "StdDev"
Case xlStDevP
    CalcType = "StdDevP"
Case xlSum
    CalcType = "Sum"
Case xlUnknown
    CalcType = "Unknown"
Case xlVar
    CalcType = "Var"
Case xlVarP
    CalcType = "VarP"
End Select
End Function

combo