Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
choop
 
Posts: n/a
Default 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
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #3   Report Post  
BardofAvon
 
Posts: n/a
Default


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

  #4   Report Post  
Alex Delamain
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Tab list position in Excel RRe Excel Discussion (Misc queries) 1 January 9th 05 01:12 AM
Combo Box goes to edit mode even if design mode is in OFF position Chas Excel Discussion (Misc queries) 0 January 7th 05 08:21 PM
Generate GetPivotData Lonnie Setting up and Configuration of Excel 1 December 10th 04 11:02 PM
I am trying to link based on a text value instead of cell position John Links and Linking in Excel 3 December 3rd 04 07:29 PM
Calculating Net Position and Average Price carl Excel Worksheet Functions 6 October 31st 04 02:08 PM


All times are GMT +1. The time now is 01:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"