getpivotdata() by position

[choop]

According to help you can reference a pivot table entry by position. for
example

sales
Name
joe 5
mike 4

How do I reference the top person assuming they will always be in the same
position? I am ordering my table by sales, so I want the top sales person's
total to be the result of my formula.

I would imagine this: (doesn't work though)
=getpivotdata(pivotName,Name[1] Sales)

I have tried lots of different quotation combinations. any ideas? Thanks

 

[Debra Dalgleish]

AFAIK, you can refer to an item by position in a calculated formula, but
not in a GetPivotData formula.

You could use the Match function to find the Name field button, and
return the value in the cell below. For example:

=GETPIVOTDATA($A$4,INDIRECT("A"&MATCH("Rep",A:A,0)+1)&" Units")

According to help you can reference a pivot table entry by position. for
example

sales
Name
joe 5
mike 4

How do I reference the top person assuming they will always be in the same
position? I am ordering my table by sales, so I want the top sales person's
total to be the result of my formula.

I would imagine this: (doesn't work though)
=getpivotdata(pivotName,Name[1] Sales)

I have tried lots of different quotation combinations. any ideas? Thanks

 

[BardofAvon]

refer to http://www.contextures.com/xlPivot06.html

to use the GenerateGetpivotdata button to stop Excel generatin
Getpivotdata functions and substitute conventional cell references
Then just reference the cell you want

 

[Alex Delamain]

Even simpler - if you create your formula using a blank cell outside the
pivot table you can the edit the formula and drag the cell reference to
where you want it in the pivot table