How do I get data from many existing pivot tables

[slhappyls]

Hi all,

How do I get data from many existing pivot tables and combine them to make a new dynamic table?

many thanks.

 

[slhappyls]

very useful

thanks a lot. But I still have problem, because I have a field called gender with "Male" & "Female" values. Now want want to calculate the sex rate. I cant use calculated filed with countif function, so I use calculated item, but it will show in very data row. I just want it show in one row called gender rate. And I have to output all the statistics in specific format. How can I deal with

 

[slhappyls]

very useful

thanks a lot. But I still have problem, because I have a field called gender with "Male" & "Female" values. Now want want to calculate the sex rate. I cant use calculated filed with countif function, so I use calculated item, but it will show in very data row. I just want it show in one row called gender rate. And I have to output all the statistics in specific format. How can I deal with

 

[slhappyls]

thanks a lot. But I still have problem, because I have a field called gender with "Male" & "Female" values. Now I want to calculate the sex rate. I cant use calculated filed with countif function, it doesnt work correctly in calculated field, so I use calculated item, but it will show in every data row. I just want it show in one row called gender rate. And I have to output all the statistics in specific format. How can I deal with?

 

[SydneyGeek]

Try playing around with the pivot table.

Add the gender field to the Row section and you will have the full breakdown by age and sex.

Now drag Age above the top left of the pivot table to the Page Field section. You will be left with the mean and variance by sex.

If you want to see the breakdown by just one age group, click the dropdown next to Age and select the age group in question. To see by all age groups again, select (All) from the list.

Another option is to leave Age alone and drag Sex above the data fields.
Then you will get the 2 data fields by sex: Female, then Male. And the totals at the bottom will have the full gender split.

Hope I haven't confused you but pivot tables are very flexible. Have a play.

Denis

 

[slhappyls]

But when I use calculated item, it can not show multiple rows of statistics

 

[SydneyGeek]

Depending on your data setup there should be no need for calculated items. Avoid them where possible, they slow down your pivot tables dramatically.

Some samples may help:
Data -- goes for 30 rows
A B C
1 Age Sex Value
2 15-25 F 66
3 36-45 M 52
4 15-25 M 46
5 26-35 F 50
6 >65 F 70
7 26-35 M 71
8 56-65 M 32
9 15-25 F 37
10 >65 F 31
11 15-25 F 53
12 15-25 M 47
13 26-35 M 31
14 46-55 M 25

Sheet1
[Table-It] version 06 by Erik Van Geit

First PT:

Book1
	E	F	G	H
3			Data
4	Age	Sex	Avg Value	Variance
5	>65	F	54.33	424.33
6		M	59.00	32.00
7	>65 Total		56.20	226.70
8	15-25	F	52.00	211.00
9		M	50.00	151.50
10	15-25 Total		50.75	147.93
11	26-35	F	50.00
12		M	52.40	489.80
13	26-35 Total		52.00	392.80
14	36-45	F	63.00
15		M	52.00
16	36-45 Total		57.50	60.50
17	46-55	F	56.00
18		M	30.00	50.00
19	46-55 Total		38.67	250.33
20	56-65	M	46.50	481.00
21	56-65 Total		46.50	481.00
22	Grand Total		50.57	246.99
Sheet1

Second PT:

Book1
	J	K	L	M	N	O	P
3		Sex	Data
4		F		M		Total Avg Value	Total Variance
5	Age	Avg Value	Variance	Avg Value	Variance
6	>65	54.33	424.33	59.00	32.00	56.20	226.70
7	15-25	52.00	211.00	50.00	151.50	50.75	147.93
8	26-35	50.00		52.40	489.80	52.00	392.80
9	36-45	63.00		52.00		57.50	60.50
10	46-55	56.00		30.00	50.00	38.67	250.33
11	56-65			46.50	481.00	46.50	481.00
12	Grand Total	54.22	172.94	48.84	283.81	50.57	246.99
Sheet1

See if this helps

Denis

 

[slhappyls]

Sorry I ve tried what u said but it can not meet my requirement. Can you tell me how to use getpivotdata to get the fields in page area. then I can select page filter at the same time. Just using newly developed dynamic table and forget my old pivot tables?

 

[SydneyGeek]

It's hard to see what you need without some idea of the fields you already have. Even without displaying data:
1. What columns are there in your table?
2. What do you need to see?

I feel like I'm operating blind at the moment. All I know is what doesn't work for you. I don't know how to get what does work, without a bit more info.

Denis

 

[slhappyls]

the following table is what I want:

 

[slhappyls]

you can see they are in specific format. A normal pivot table can not meet the requirements