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

mikestewart27

New Member
Joined
Sep 29, 2006
Messages
3
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mikestewart27

New Member
Joined
Sep 29, 2006
Messages
3
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))
 

mikestewart27

New Member
Joined
Sep 29, 2006
Messages
3
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
 

Forum statistics

Threads
1,136,878
Messages
5,678,306
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top