Pivot Tables

[Isabella]

Hi,

Could someone please show me how i can perform a pivot table that will give me the same result as show in Col J-P. I want to avoid using formula if possible.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4											2-29			30-59
5	Type	Amount	CCY	Age	Source		CCY	Rate		Source	No. of items	Value (AUD)	ABS (AUD)	No. of items	Value (AUD)	ABS (AUD)
6	SCR	8268.57	AUD	15	Team1		AUD	1		Team1	3	29,268.57	29268.57
7	SCR	20000	AUD	20	Team1		EUR	0.75581		Team2	1	6,844.94	6844.94067
8	SCR	1000	AUD	5	Team1		GBP	0.645411		Team3	0	0.00	0
9	SCR	4417.8	GBP	7	Team2		JPY	82.56223		Team4	17	29,314.60	1803231.35
10	SCR	2208.9	GBP	30	Team2		USD	1.00655
11	SCR	100000	AUD	40	Team3
12	LCR	1163.22	EUR	12	Team4
13	LDR	-3320.14	EUR	39	Team4
14	LCR	9646.51	EUR	15	Team4
15	LDR	-12618.8	EUR	40	Team4
16	LCR	476017.8	EUR	15	Team4
17	LDR	-1688137	JPY	38	Team4
18	LCR	7187517	JPY	4	Team4
19	LDR	-6303971	JPY	15	Team4
20	LCR	108431.3	USD	15	Team4
21	LCR	15652.36	USD	15	Team4
22	LDR	-280467	USD	17	Team4
23	LCR	52025.14	USD	17	Team4
24	LCR	7589.03	USD	17	Team4
25	LCR	1965.98	EUR	3	Team4
26	LDR	-98555.5	EUR	17	Team4
27	LDR	-33658.3	EUR	17	Team4
28	LDR	-19670	EUR	10	Team4
29	LDR	-247310	EUR	17	Team4
30	LDR	-2068.46	EUR	13	Team4
31	LDR	-800	EUR	28	Team4
Sheet1

 

[Fazza]

 

[Isabella]

Hi Fazza, when i save my workbook on a different computer, and refresh pivot , i get a message "Could not find installable ISAM? Why does this happen?

 

[Fazza]

hi, Isabella.

I don't know. Maybe related to different Excel versions? (I work only in Excel 2003.) It seems that you're working in several Excel versions. A quick search via Google found

http://support.microsoft.com/kb/209805
http://social.msdn.microsoft.com/Fo...s/thread/62ee4978-ca29-48d3-9367-6dd4ab639d2d
it might be as simple as http://it.toolbox.com/blogs/sharepoint-blog/could-not-find-installable-isam-17062

If it is a little difficult to fix, suggest alternatively create the pivot table afresh as required via VBA.

HTH

 

[Isabella]

I managed to fox it, but when i refresh via VBA i get a msg [Microsoft] [OBDC Excel Driver] The connection for your viewing your linked Microsoft Excel Worksheet was lost.

When i refresh manually it works. Even when record a macro, it does not record the refresh part.

Sub RefreshPivot()

Dim pt As PivotTable

    Set pt = ThisWorkbook.Worksheets(gstrTeam).PivotTables("Team")

    pt.RefreshTable

End Sub

hi, Isabella.

I don't know. Maybe related to different Excel versions? (I work only in Excel 2003.) It seems that you're working in several Excel versions. A quick search via Google found

http://support.microsoft.com/kb/209805
http://social.msdn.microsoft.com/Fo...s/thread/62ee4978-ca29-48d3-9367-6dd4ab639d2d
it might be as simple as http://it.toolbox.com/blogs/sharepoint-blog/could-not-find-installable-isam-17062

If it is a little difficult to fix, suggest alternatively create the pivot table afresh as required via VBA.

HTH