Pivot table - SUMIFS Issue

[subrahmanyam85]

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
==========
[TABLE="width: 423"]
<tbody>[TR]
[TD]Year[/TD]
[TD]R_Year[/TD]
[TD]Code[/TD]
[TD]Currency[/TD]
[TD]Category[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2011[/TD]
[TD]A1[/TD]
[TD]GBP[/TD]
[TD]Cat-1[/TD]
[TD="align: right"]-29.01[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2011[/TD]
[TD]A1[/TD]
[TD]GBP[/TD]
[TD]Cat-3[/TD]
[TD="align: right"]-45.24[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2011[/TD]
[TD]AG[/TD]
[TD]GBP[/TD]
[TD]Cat-2[/TD]
[TD="align: right"]-125.81[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2011[/TD]
[TD]AG[/TD]
[TD]AUD[/TD]
[TD]Cat-4[/TD]
[TD="align: right"]-7.91[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2012[/TD]
[TD]AH[/TD]
[TD]AUD[/TD]
[TD]Cat-4[/TD]
[TD="align: right"]1,568.01[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2012[/TD]
[TD]AH[/TD]
[TD]AUD[/TD]
[TD]Cat-1[/TD]
[TD="align: right"]223.93[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2012[/TD]
[TD]AG[/TD]
[TD]USD[/TD]
[TD]Cat-3[/TD]
[TD="align: right"]-196.26[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2012[/TD]
[TD]AG[/TD]
[TD]USD[/TD]
[TD]Cat-1[/TD]
[TD="align: right"]-12.34[/TD]
[/TR]
[TR]
[TD]1993[/TD]
[TD]RY-2013[/TD]
[TD]AG[/TD]
[TD]USD[/TD]
[TD]Cat-2[/TD]
[TD="align: right"]2,446.10[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]


Report Layout
=========
[TABLE="width: 595"]
<tbody>[TR]
[TD]Year
[/TD]
[TD]R_Year
[/TD]
[TD]Code
[/TD]
[TD]Currency
[/TD]
[TD]Cat-1
[/TD]
[TD]Cat-2
[/TD]
[TD]Cat-3
[/TD]
[TD]Cat-4
[/TD]
[/TR]
[TR]
[TD]1993
[/TD]
[TD]RY-2011
[/TD]
[TD]A1
[/TD]
[TD]GBP
[/TD]
[TD]-29.01
[/TD]
[TD][/TD]
[TD]-45.24
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1993
[/TD]
[TD]RY-2011
[/TD]
[TD]AG
[/TD]
[TD]GBP
[/TD]
[TD][/TD]
[TD]-125.81
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1993
[/TD]
[TD]RY-2011
[/TD]
[TD]AG
[/TD]
[TD]AUD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-7.91
[/TD]
[/TR]
[TR]
[TD]1993
[/TD]
[TD]RY-2012
[/TD]
[TD]AH
[/TD]
[TD]AUD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1,568.01
[/TD]
[/TR]
[TR]
[TD]1993
[/TD]
[TD]RY-2012
[/TD]
[TD]AG
[/TD]
[TD]USD
[/TD]
[TD]-12.34
[/TD]
[TD][/TD]
[TD]-196.26
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1993
[/TD]
[TD]RY-2013
[/TD]
[TD]AG
[/TD]
[TD]USD
[/TD]
[TD][/TD]
[TD]2,446.10
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[subrahmanyam85]

Can you please suggest a best way to this or please let me know how to optimize SUMIFS.

 

[RoryA]

Why not use GETPIVOTDATA? That's what it's for.

 

[Andrew Poulsom]

Or create another pivot table with Category in the Column area.

 

[subrahmanyam85]

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.

 

[Andrew Poulsom]

Here's a pivot table that looks like your report:

Excel 2010
	A	B	C	D	E	F	G	H
4	Year	R_Year	Code	Currency	Cat-1	Cat-2	Cat-3	Cat-4
5	1993	RY-2011	A1	GBP	-29.01		-45.24
6	1993	RY-2011	AG	AUD				-7.91
7	1993	RY-2011	AG	GBP		-125.81
8	1993	RY-2012	AG	USD	-12.34		-196.26
9	1993	RY-2012	AH	AUD	223.93			1568.01
10	1993	RY-2013	AG	USD		2446.1
Sheet4

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

 

[subrahmanyam85]

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

 

[Andrew Poulsom]

For a tutorial on GetPivotData see:

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