Reference a Cell within a Pivot Table within a GetPivotData formula

[cabbey77]

Hello,

I have a Pivot Table whereby I am performing a running Percentage outside of the pivot table.
The Table is as follows:

Hub (grouped) Comp  Desc  Qty by Wk  Total

The running subtotal formula works for the 1st hub but not subsequent due to the hard reference to $AE$5.

=IF(ISBLANK(A5),((AE5/$AE$5)+IF(ISBLANK(A4),AG4,0)),TEXT(AE5,"####"))

I need to replace the $AE$5 with a reference the total for whatever hub group is in the row.

Is this clear as mud?

Thanks in advance!

~C

 

[SkipVought]

Hi,

Why not use the source data to calculate the sum using SUMIF or SUMIFS function.

 

[kjv1611]

I agree with Skip 100% on this one. I can tell you from experience that referring to cells within a Pivot table, if that Pivot table could possibly change in the future is a bad idea. You'll wind up kicking yourself when you're editing.. and editing... and editing.. and editing... down the road.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57