I think it is a bug in Excel 2007. Otherwise, if someone has a solution I'd
be happy to hear that ;-)

Here is the problem:
I got a table with lots of data. In order to extract the relevant
information I inserted a pivot table. Then I wanted to display the extracted
information within a chart. For that, I marked the pivot table and inserted a
3D block chart.
Now, I wanted to modify the pivot extraction a little bit and I'd like to
see the result of my modification inside the chart. As long as I do it in the
original pivot table everything works fine.
Now, the problem comes: I'd like to permanently keep both pivot extractions
inside the workbook. Therefore, I copied the sheet containing the pivot table
and the chart to a second sheet. Now, I have 3 sheets inde the workbook: the
original data sheet, the 1st pivot sheet and the copied pivot sheet.
At first I recognized that the chart in the copied pivot sheet always refers
to the data inside the 1st pivot sheet. The problem is now, when I try to
change the data source ("select data source" --> "chart data range") I see
that the input field is greyed out. So, I cannot change the data source of
the chart in the copied sheet.
The only possibility I found is to delete the chart in the copied sheet an
to create it completely new from scratch based on the pivot table of the
copied sheet.

Any ideas?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

You cannot change the data source of a pivot chart. It points to the
original pivot table, and that's that. But you can try to work around this
issue.

Save the sheet with the pivot table and chart as a new workbook with a
different name. Reopen the original workbook, then open the saved-as
workbook, and move the sheet into the original workbook. This gives you two
pivot tables, one each with its own chart. Repeat opening the saved-as
workbook and moving it into the main workbook as required.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"MrN" <(E-Mail Removed)> wrote in message
news:09027F37-9A9E-4782-8D85-(E-Mail Removed)...
>I think it is a bug in Excel 2007. Otherwise, if someone has a solution I'd
> be happy to hear that ;-)
>
> Here is the problem:
> I got a table with lots of data. In order to extract the relevant
> information I inserted a pivot table. Then I wanted to display the
> extracted
> information within a chart. For that, I marked the pivot table and
> inserted a
> 3D block chart.
> Now, I wanted to modify the pivot extraction a little bit and I'd like to
> see the result of my modification inside the chart. As long as I do it in
> the
> original pivot table everything works fine.
> Now, the problem comes: I'd like to permanently keep both pivot
> extractions
> inside the workbook. Therefore, I copied the sheet containing the pivot
> table
> and the chart to a second sheet. Now, I have 3 sheets inde the workbook:
> the
> original data sheet, the 1st pivot sheet and the copied pivot sheet.
> At first I recognized that the chart in the copied pivot sheet always
> refers
> to the data inside the 1st pivot sheet. The problem is now, when I try to
> change the data source ("select data source" --> "chart data range") I see
> that the input field is greyed out. So, I cannot change the data source of
> the chart in the copied sheet.
> The only possibility I found is to delete the chart in the copied sheet an
> to create it completely new from scratch based on the pivot table of the
> copied sheet.
>
> Any ideas?
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
> this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...excel.charting