macro to sum based on a relative named label (pivot table)

[mikestewart27]

Hello

I have an excel pivot table with a large quantity of figures in it. The data I want to sum is a series of numbers in the 'Row' section of the pivot table (i cannot put this in the data section of the pivot table as I want both the details of the numbers and one field that totals the section).

I can manually do a sum by typing =sum(myLabelName) in the formula box in a blank field and this will automatically update as the pivot table is expanded and contracted. However as i move the fields about in the pivot table the formula will not follow my label, but instead do a sum of whatever is now in that column.

Is there a macro or formula or method that can identify what column my field is in and perform a calucation on that field even if pivot table is reorganised?

Any help (today would be fantastic) would be much appreciated!!

Cheers
Mike

 

[GlennUK]

Look at the GETPIVOTDATA function.

 

[mikestewart27]

Look at the GETPIVOTDATA function.

Cheers for the tiup. I was actually just looking at it but was having a few issues.

i know the formula for it is =GETPIVOTDATA(pivot_table, name) but i am getting a #name error

Here is the formula I am using
=GETPIVOTDATA(myTableName, SUM(myLabel))

 

[GlennUK]

What is "myTableName" precisely?

 

[mikestewart27]

got it fixed. made a amcro to go through all of the rows, check for the name, perform a calcuation on this and place it in a field two cells above the label name, with a label above that showing the title of the sum


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim a As Integer
ActiveSheet.Range("a7").Select
For a = 1 To 10
Select Case ActiveCell
Case "myLabel"
ActiveCell.Offset(-2, 0).Activate
ActiveCell = "=SUM(myLabel)"
ActiveCell.Offset(-1, 0).Activate
ActiveCell = "SUM(myLabel)"
End Select
ActiveCell.Offset(3, 1).Activate
Next a
End Sub