getpivotdata() by position
 

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

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")

choop wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

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


--
BardofAvon
------------------------------------------------------------------------
BardofAvon's Profile: http://www.excelforum.com/member.php...o&userid=16075
View this thread: http://www.excelforum.com/showthread...hreadid=275069

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


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=275069