copy a GETPIVOTDATA formula ?

[liveinhope]

With "normal" formulas when you copy the formula down to rows below the relative cell adresses change to refer to the data on the other rows.

But With GETPIVOTDATA copy down copies like absolute cell addresses (my pivot table and GETPIVOTDATA formula below)
In the end I entered (by pointing ) the formula seperately each of the row values "North","South" "East" "West"

Not too bad in this instance as there were only 4 different row values but if there were a large number that would be a real pain !

Is there a way round this ?
formula
=GETPIVOTDATA("AVG SHIPD",$F$1,"br","east")/GETPIVOTDATA("AVG ORDER",$F$1,"br","east")

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

	Values
Row Labels	Sum of ordered	Sum of shipped	AVG ORDER	AVG SHIPD
east	125	40	41.6666666666667	13.3333333333333
north	170	55	56.6666666666667	18.3333333333333
south	70	55	35	27.5
west	50	35	25	17.5
Grand Total	415	185	41.5	18.5

<COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 2048" width=112><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 1865" width=102><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 1609" width=88><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 1865" width=102><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 2340" width=128><TBODY>
</TBODY>

 

[Jerry Sullivan]

Yes, you can use relative references in GetPivotData arguments.

For your example, if the cell displaying "east" is F3, then your formula could be:

=GETPIVOTDATA("AVG SHIPD",$F$1,"br",$F3")/GETPIVOTDATA("AVG ORDER",$F$1,"br",$F3)

This could be copied down to reference $F4 (north), $F5 (south)...

 

[liveinhope]

beautiful ! as I said point-and-enter for each line isnt too bad in this example but would drive me mad if I had to do it

for something like a Pivot table with 50 US states!
thank you

 

[Handiwork]

I understand how to make the modifications to the formula posted by liveinhope. My GetPivot formula looks completely different and I can't figure out how to modify it. The "FW" is Fiscal Week. I want to copy the formula down for all 52 weeks of the year. I have tried a lot of variations but can't get it to work. How do I modify my formula to copy it down?

GETPIVOTDATA("[Measures].[TotalHours]",$N$7,"tbl_calendar_labor].[FW]","[tbl_calendar_labor].[FW].&[1.]")/CODE]

 

[Jerry Sullivan]

The structured field references in brackets indicate you probably have an OLAP data source.

Assuming that you have the value "1" in A1, "2" in A2 and so on, try this syntax...

=GETPIVOTDATA("[Measures].[TotalHours]",$N$7,"[tbl_calendar_labor].[FW]","[tbl_calendar_labor].[FW].&["& A$1 &".]")