Pivot table - SUMIFS Issue

subrahmanyam85

New Member
Joined
Aug 26, 2014
Messages
20
Hi,

I have pivot table and have to show the data from into another sheet as Report format.
Format should not change.We are having only 4 categories.We have to show the catergory values as Header in Report (as shown below)

By using Excel macros once the pivot table get refresh I am bringing distinct values of Year,R_Year,Code,Currency in to Report sheet.

I have done the calculations by using SUMIFS.When the pivot table rows are increasing calculations are repeating and taking long time.

Can we do it without using SUMIFS?

Please suggest me the best way to achive it.Below is format of my data.

Pivot Table
==========
YearR_YearCodeCurrencyCategoryAmount
1993RY-2011A1GBPCat-1-29.01
1993RY-2011A1GBPCat-3-45.24
1993RY-2011AGGBPCat-2-125.81
1993RY-2011AGAUDCat-4-7.91
1993RY-2012AHAUDCat-41,568.01
1993RY-2012AHAUDCat-1223.93
1993RY-2012AGUSDCat-3-196.26
1993RY-2012AGUSDCat-1-12.34
1993RY-2013AGUSDCat-22,446.10

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>


Report Layout
=========
Year
R_Year
Code
Currency
Cat-1
Cat-2
Cat-3
Cat-4
1993
RY-2011
A1
GBP
-29.01
-45.24
1993
RY-2011
AG
GBP
-125.81
1993
RY-2011
AG
AUD
-7.91
1993
RY-2012
AH
AUD
1,568.01
1993
RY-2012
AG
USD
-12.34
-196.26
1993
RY-2013
AG
USD
2,446.10

<tbody>
</tbody>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,435
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Why not use GETPIVOTDATA? That's what it's for. ;)
 
Upvote 0

subrahmanyam85

New Member
Joined
Aug 26, 2014
Messages
20
Thanks for your reply RoryA and Andrew.

I shouldn't change the format of Report Layout.That's why I am proceeding with GETPIVOTDATA function.
Pivot table is in another sheet, I have to show those values in another sheet.

To get the value of Cat-1,I am writing syntax in my Report Layout Sheet like below.But it is giving some reference error.

Amount,Year,R_Year are from pivot table.

=GETPIVOTDATA("Amount",PivotTable!F3,"Year",A2,"R_Year","B2")

Please refer Pivot Table and Report Layout.
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here's a pivot table that looks like your report:


Excel 2010
ABCDEFGH
4YearR_YearCodeCurrencyCat-1Cat-2Cat-3Cat-4
51993RY-2011A1GBP-29.01-45.24
61993RY-2011AGAUD-7.91
71993RY-2011AGGBP-125.81
81993RY-2012AGUSD-12.34-196.26
91993RY-2012AHAUD223.931568.01
101993RY-2013AGUSD2446.1
Sheet4


Your macro can create it and convert it to values using Copy/Paste Special.
 
Upvote 0

subrahmanyam85

New Member
Joined
Aug 26, 2014
Messages
20
I am not good in Macros Andrew,Thats why I am using GetPivotdata function.

Can we refer a Pivot table from another sheet in GetPivotdata?

I have already googled about this but I didn't get proper answere any where.

In on site they suggetsted the syntax like below. Its not working.

=GETPIVOTDATA("Amount",PivotTable!F3,"Year",A2,"R_Year","B2")
 
Upvote 0

Forum statistics

Threads
1,191,627
Messages
5,987,756
Members
440,107
Latest member
stefanyelas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top