Chart from Pivot table

[Guest]

Dear All

I am using Excel 97 and Pivot charts dont yet exist on this.

I have a pivot table of staff members in the rows, and in the data field I have Sum of Spend and Average of Spend. In the Page field I have Team, Spend Month and Spend Year.

Now I want to create a chart of the staff members and there Sum of Spend but I cannot do a chart from this pivot because I have Average spend in there. If I do a seperate pivot table for this chart alone the options I select in page wont reflect in the other pivot table and essential the Chart.

Does anyone know how I can create a chart linked to the controls on my pivot but not to show Average of Spend just Sum of Spend?

 

[Debra Dalgleish]

You could select only the staff names, and Sum of Spend amounts, and
create a chart from that.

Or create a second pivot table, with only Sum of Spend, and use the
following code, adapted from a posting by Robert Rosenberg. It changes
all Pivot Tables if the page is changed on the first PT. You could
revise it to suit your layout. As noted in the code, place the code on
the module for the worksheet which contains the first Pivot Table. Or,
add the code to each sheet with a pivot table, so a change to one will
change all (use a different module level variable name on each sheet).

Dim mvPivotPageValue As Variant
Private Sub Worksheet_Calculate()
'by Robert Rosenberg 2000/01/11
''I use a module level variable (see above) to keep track of
''the last selection from the Page Field.
''This routine was place in the Worksheet
''containing the PivotTable's code module.
Dim pvt As PivotTable
Dim pvt2 As PivotTable
Dim ws As Worksheet

Set pvt = ActiveSheet.PivotTables(1)
If LCase(pvt.PivotFields("Year").CurrentPage) _
<> LCase(mvPivotPageValue) Then
For Each ws In ActiveWorkbook.Worksheets
For Each pvt2 In ws.PivotTables
'The Page Field was changed
Application.EnableEvents = False
pvt.RefreshTable
mvPivotPageValue = _
pvt.PivotFields("Year").CurrentPage
pvt2.PageFields("Year").CurrentPage _
= mvPivotPageValue
Application.EnableEvents = True
Next pvt2
Next ws
End If
End Sub
'====================================