PDA

View Full Version : Use VBA for totals for Pivot Chart



Laavista
09-19-2010, 08:22 PM
I have a pivot table and a pivot stack chart. I need the totals displayed at the top of each stack.

If it was a regular chart, I could use the 'add data' or alternate series to add the total as a line chart and make the line chart invisible with the data labels visible. However, I cannot use a regular chart as the data is very dynamic and changes regularly.

In the example below, I need 3 on top of Co1, 1 on top of Co2 and 1 on top of Co3

I thought of using VBA to somehow add the totals. Any suggestions?

Sample data:
Company Product
CO1.........AA
CO2........ AA
CO3........ BB
CO1........ CC
CO1........ AA

Pivot would look something like:
........AA.. BB.. CC.... GRAND TOTAL
CO1... 1... 1.... 1............... 3
CO2... 1........................... 1
CO3..........1......................1

The pivot chart needs to look something like:

3........1...... 1
[ ]
[ ]
[ ].... [ ]..... [ ]
CO1.. CO2.. CO3