Pivot Tables and Graphs (charts)

[learnlearn52]

Background:

I have a Pivot Table (PT) which is updated monthly. A chart is tied to the
data in the PT. The X-axis contains the months and the Y-axis contains the
respective data of the months.

Question: How can the chart be updated automatically (this should include
the latest monthly data) whenever the PT is updated (refreshed)?

Thanks in advance.

 

[learnlearn52]

Thanks for the response. I have made the raw data "dynamic." I use the
"Refresh" feature for the PT and update. When the PT is updated, new data are
added i.e., an additional column is added. The chart (based on the Source
data which is from the PT) is is not picking up the new column. Instead it
is still displaying the last but one column from the PT. I would like the
graph to "keep pace" with the PT. Hope this is making sense.

Thanks in advance.

 

[Shane Devenshire]

Hi,

This problem suggests that your data setup is incorrect for a pivot table.
You should not get a new field everytime you add new data. Please show us a
sample of the raw data area before and after the addition of a new month's
data. Don't worry about the pivot table's structure at present.

 

[learnlearn52]

Shane:

Thanks for the response. Here is an example of the PT. The months are in the
first row along with the Grand Total. The first column is what I am calling
as Defect 1, Defect 2, and Defect 3 (there could be more). The columns
contain the number of Defects per month.

Item Jan09 Feb09 Mar09 Grand Total

Defect1 1 1 2 4
Defect2 1 1
Defect3 3 1 1 5

Total 4 3 3 10

This is how the PT looks like from the raw data. Now, I plot a bar chart
showing the last month's (Mar09) data along with the Grand Total data i.e.,
the last two columns are plotted with the Defects along the X-axis.
Let us assume we updated the raw data with info' for the month of Apr09. The
PT can be updated easily with Apr09 showing up between Mar09 and Grand Total.
The chart shows the data from Mar09 and Apr09 instead of Apr09 and Grand
Total. One can manually update the chart by right-clicking the chart and
using the Source Data Option to choose the last two columns of the updated PT.

Question: Is there a way to update the chart automatically once the PT is
refreshed?

Hope this is making sense. Thanks once again (to you and others) for your
patience.