Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 13th 07, 10:16 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 1
Default Calculating from values in pivot tables

I want to perform a calculation on data in a pivot table which I then copy
down row by row but when I copy the formula down it does not work as it picks
up the criteria in the first row of the pivot table.

In old versions of Excel it just copied the simple formula down as opposed
to putting in the pivot table criteria - how do I replicate this?

  #2   Report Post  
Old September 13th 07, 12:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,441
Default Calculating from values in pivot tables

Owen,

You need to type the cell reference into the formula rather than using cell selection to pick up the
cell. Clicking on a cell in the pivot table defaults to the GETPIVOTDATA function....

HTH,
Bernie
MS Excel MVP


"Owen Peck" <Owen wrote in message
...
I want to perform a calculation on data in a pivot table which I then copy
down row by row but when I copy the formula down it does not work as it picks
up the criteria in the first row of the pivot table.

In old versions of Excel it just copied the simple formula down as opposed
to putting in the pivot table criteria - how do I replicate this?



  #3   Report Post  
Old September 13th 07, 02:06 PM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Calculating from values in pivot tables

Try Debra's page for a way to turn the GETPIVOTDATA "feature" off:
http://www.contextures.com/xlPivot06.html

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
  #4   Report Post  
Old September 13th 07, 03:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default Calculating from values in pivot tables

Hi Owen

Whilst the other 2 responses you have are correct, using a cell reference
rather than GetPivotData will result in errors, if the layout of the PT
alters.

You can use the GetPIvotData feature, but having generated the formula,
amend the value where it says the row name, to $A4 (or whatever is the cell
for the first row item you are trying to retrieve, then as you copy down it
will adjust automatically.

Refer to the same reference as Max gave you
http://www.contextures.com/xlPivot06.html
but further down the page, in the section GetPivotData Formula

For example, instead of
=GETPIVOTDATA("Units",$A$3,"Region","Alberta")
you should use
=GETPIVOTDATA("Units",$A$3,"Region",$A5)
In the first case, Alberta would remain a constant as you copy down, in the
second case it would change from Alberta to Ontario to Quebec etc.

--
Regards
Roger Govier



"Owen Peck" <Owen wrote in message
...
I want to perform a calculation on data in a pivot table which I then copy
down row by row but when I copy the formula down it does not work as it
picks
up the criteria in the first row of the pivot table.

In old versions of Excel it just copied the simple formula down as opposed
to putting in the pivot table criteria - how do I replicate this?





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
Pivot Tables - Ranking Values Jeff Reese Excel Discussion (Misc queries) 1 May 1st 07 03:44 PM
Calculating in Pivot Tables Teresa Excel Discussion (Misc queries) 2 July 18th 06 07:18 PM
Calculating fields in pivot tables kosciosco Excel Discussion (Misc queries) 2 June 6th 06 02:45 PM
Pivot tables with zero/null values Todd1 Excel Discussion (Misc queries) 4 March 28th 06 12:45 PM
Calculating percentages in pivot tables on subtotals David Ruderman - Chapman University Excel Worksheet Functions 1 November 24th 05 04:54 PM


All times are GMT +1. The time now is 09:53 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017