pivot tables: changing 'sum of' to 'average of' & cycling through pages
 

hi... I'd love some pivot table help!

firstly: -

when i add fields to the pivot table (eg, drag in using the wizard), they
are always 'sum of' or 'count of', but i normaly want 'average of' is there
any way to adjust this (ie, change an option in preferences) or is there
some vba i can use to change all the fields in the data area to 'average
of'? whenever i've tried, it always seems to refer to all available fields
rather than those in the pivot-table.

secondly: -

i have collected data from several different production sites and i want to
make several summary pivot tables quickly/easily; can sometime tell me how i
can use vba loop through each site creating a new worksheet of sumary data
for each of the sites?

thanks for any pointers you can give me,

Tim

pivot tables: changing 'sum of' to 'average of' & cycling through
 

The default when you drag a new dimension onto the table is sum. You can
right click the dimension and select Field Setting and change it to average
(I assume you already knew this). To the best of my knowledge there is no way
to adjust what type of aggregation will be the default. The default is sum
and that is just the way it is...

As for your second question, if I understand you correctly, you want to
create a seperate worksheet for each member in a dimension. The answer
depends on whether you are using xl2000 orlater (pivot tables changed in
xl2002). If you are in 2000 then place the dimension you would like to
cascade in the selection criteria above the table. Right click and select
Show Pages. In xl2002 do the same thing but you select Show Pages from the
pivot table drop down on the pivot table menu.
--
HTH...

Jim Thomlinson


"Tim" wrote:

hi... I'd love some pivot table help!

firstly: -

when i add fields to the pivot table (eg, drag in using the wizard), they
are always 'sum of' or 'count of', but i normaly want 'average of' is there
any way to adjust this (ie, change an option in preferences) or is there
some vba i can use to change all the fields in the data area to 'average
of'? whenever i've tried, it always seems to refer to all available fields
rather than those in the pivot-table.

secondly: -

i have collected data from several different production sites and i want to
make several summary pivot tables quickly/easily; can sometime tell me how i
can use vba loop through each site creating a new worksheet of sumary data
for each of the sites?

thanks for any pointers you can give me,

Tim

pivot tables: changing 'sum of' to 'average of' & cycling through
 

Jim,

Point 1: yes, you assumed right! It was a 'shot in the dark' that there
would be an easy way of changing the default. I 'm just wondering if anyone
can advise how i can reference each of the chosen fields in the table to
change them to 'average of' - like i said originally, whenever i tried, i
could only actually refer to the available fields, not the chosen ones (i
hope that makes some kind of sense!)

Point 2: Thank you very much... I never knew that! That's exactly what i
wanted!

IDH*,

Tim

* = It Did Help

"Jim Thomlinson" wrote in message
...
The default when you drag a new dimension onto the table is sum. You can
right click the dimension and select Field Setting and change it to
average
(I assume you already knew this). To the best of my knowledge there is no
way
to adjust what type of aggregation will be the default. The default is sum
and that is just the way it is...

As for your second question, if I understand you correctly, you want to
create a seperate worksheet for each member in a dimension. The answer
depends on whether you are using xl2000 orlater (pivot tables changed in
xl2002). If you are in 2000 then place the dimension you would like to
cascade in the selection criteria above the table. Right click and select
Show Pages. In xl2002 do the same thing but you select Show Pages from the
pivot table drop down on the pivot table menu.
--
HTH...

Jim Thomlinson


"Tim" wrote:

hi... I'd love some pivot table help!

firstly: -

when i add fields to the pivot table (eg, drag in using the wizard),
they
are always 'sum of' or 'count of', but i normaly want 'average of' is
there
any way to adjust this (ie, change an option in preferences) or is there
some vba i can use to change all the fields in the data area to 'average
of'? whenever i've tried, it always seems to refer to all available
fields
rather than those in the pivot-table.

secondly: -

i have collected data from several different production sites and i want
to
make several summary pivot tables quickly/easily; can sometime tell me
how i
can use vba loop through each site creating a new worksheet of sumary
data
for each of the sites?

thanks for any pointers you can give me,

Tim

pivot tables: changing 'sum of' to 'average of' & cycling thro
 

This code loops through all of the visible fields and attepts to change the
aggregation to average (xl2002)...

Sub test()
Dim pvt As PivotTable
Dim pvf As PivotField

Set pvt = ActiveSheet.PivotTables(1)
On Error Resume Next
For Each pvf In pvt.VisibleFields
pvf.Function = xlAverage
Next pvf
On Error GoTo 0
End Sub
--
HTH...

Jim Thomlinson


"Tim" wrote:

Jim,

Point 1: yes, you assumed right! It was a 'shot in the dark' that there
would be an easy way of changing the default. I 'm just wondering if anyone
can advise how i can reference each of the chosen fields in the table to
change them to 'average of' - like i said originally, whenever i tried, i
could only actually refer to the available fields, not the chosen ones (i
hope that makes some kind of sense!)

Point 2: Thank you very much... I never knew that! That's exactly what i
wanted!

IDH*,

Tim

* = It Did Help

"Jim Thomlinson" wrote in message
...
The default when you drag a new dimension onto the table is sum. You can
right click the dimension and select Field Setting and change it to
average
(I assume you already knew this). To the best of my knowledge there is no
way
to adjust what type of aggregation will be the default. The default is sum
and that is just the way it is...

As for your second question, if I understand you correctly, you want to
create a seperate worksheet for each member in a dimension. The answer
depends on whether you are using xl2000 orlater (pivot tables changed in
xl2002). If you are in 2000 then place the dimension you would like to
cascade in the selection criteria above the table. Right click and select
Show Pages. In xl2002 do the same thing but you select Show Pages from the
pivot table drop down on the pivot table menu.
--
HTH...

Jim Thomlinson


"Tim" wrote:

hi... I'd love some pivot table help!

firstly: -

when i add fields to the pivot table (eg, drag in using the wizard),
they
are always 'sum of' or 'count of', but i normaly want 'average of' is
there
any way to adjust this (ie, change an option in preferences) or is there
some vba i can use to change all the fields in the data area to 'average
of'? whenever i've tried, it always seems to refer to all available
fields
rather than those in the pivot-table.

secondly: -

i have collected data from several different production sites and i want
to
make several summary pivot tables quickly/easily; can sometime tell me
how i
can use vba loop through each site creating a new worksheet of sumary
data
for each of the sites?

thanks for any pointers you can give me,

Tim

pivot tables: changing 'sum of' to 'average of' & cycling thro
 

Yep, that works perfectly and I see where i was going wrong

Thanks Jim,

Tim

"Jim Thomlinson" wrote in message
...
This code loops through all of the visible fields and attepts to change
the
aggregation to average (xl2002)...

Sub test()
Dim pvt As PivotTable
Dim pvf As PivotField

Set pvt = ActiveSheet.PivotTables(1)
On Error Resume Next
For Each pvf In pvt.VisibleFields
pvf.Function = xlAverage
Next pvf
On Error GoTo 0
End Sub
--
HTH...

Jim Thomlinson


"Tim" wrote:

Jim,

Point 1: yes, you assumed right! It was a 'shot in the dark' that there
would be an easy way of changing the default. I 'm just wondering if
anyone
can advise how i can reference each of the chosen fields in the table to
change them to 'average of' - like i said originally, whenever i tried,
i
could only actually refer to the available fields, not the chosen ones
(i
hope that makes some kind of sense!)

Point 2: Thank you very much... I never knew that! That's exactly what
i
wanted!

IDH*,

Tim

* = It Did Help

"Jim Thomlinson" wrote in
message
...
The default when you drag a new dimension onto the table is sum. You
can
right click the dimension and select Field Setting and change it to
average
(I assume you already knew this). To the best of my knowledge there is
no
way
to adjust what type of aggregation will be the default. The default is
sum
and that is just the way it is...

As for your second question, if I understand you correctly, you want
to
create a seperate worksheet for each member in a dimension. The answer
depends on whether you are using xl2000 orlater (pivot tables changed
in
xl2002). If you are in 2000 then place the dimension you would like to
cascade in the selection criteria above the table. Right click and
select
Show Pages. In xl2002 do the same thing but you select Show Pages from
the
pivot table drop down on the pivot table menu.
--
HTH...

Jim Thomlinson


"Tim" wrote:

hi... I'd love some pivot table help!

firstly: -

when i add fields to the pivot table (eg, drag in using the wizard),
they
are always 'sum of' or 'count of', but i normaly want 'average of'
is
there
any way to adjust this (ie, change an option in preferences) or is
there
some vba i can use to change all the fields in the data area to
'average
of'? whenever i've tried, it always seems to refer to all available
fields
rather than those in the pivot-table.

secondly: -

i have collected data from several different production sites and i
want
to
make several summary pivot tables quickly/easily; can sometime tell
me
how i
can use vba loop through each site creating a new worksheet of
sumary
data
for each of the sites?

thanks for any pointers you can give me,

Tim