Pivot chart problem in Excel 2010

**phishneslo** · 04-01-2011, 03:52 PM

i have one table of data formatted as a table.

when i click on a cell in the table and insert > pivot chart, and place that chart / table on an existing worksheet, the chart is linked to a different pivot table (not the new one that was just created)...

I have a sheet that contains the pivot tables for about 10 charts on various other worksheets.

i am using a template as a default chart.

everything worked fine until now, but now, when i try to create a chart the same way i had been (by clicking any cell in the table, and selecting "insert" "pivot chart") a new chart and table are created, but the chart is linked to "PivotTable1", and the new table, which usually corresponds to the new chart, is "PivotTable12" or something... If i click on the chart and change the pivot data, it alters the results in "PivotTable1", thus altering the chart which is already linked to that table.

somehow, when i try to create a new pivot chart, i get a new chart and table, but the chart is not linked to the new table...

hope this makes sense...

thanks for the help,

Phil

**MarvinP** · 04-01-2011, 08:08 PM

Hi Phil and welcome to the forum,

Everytime you create a new Pivot Table and put it on a sheet, it gets a number. That number is normally one bigger than the last pivot table you put on that sheet. It sounds like you have put 11 previous pivots on this sheet as you are up to number 12. If you remove a pivot table on a sheet or delete the columns it is in, I believe this will really remove it and start counting over.

The way to test this theory is to go to the VBA editor and in the immediate window type:

debug.print Activesheet.PivotTables.Count

and press enter. The number of Pivot Tables on the current sheet will be displayed.
Then type

Debug.Print ActiveSheet.PivotTables(1).Name

and press enter. This will display the name of the first pivot table on your sheet. If you have more than 1 then change the 1 to a 2 and get the name of the second one.

I'm not sure what your actual question is in this post but here are some things to try.

I've created a Named Range for my data and use this name when dealing with Pivot Tables. When I create a Pivot Table or Pivot Chart, I use this Name instead of the Absolute range of cells. This might solve your problem

If your data grows or contracts then I'd use a Dynamic Named Range whoes name would be used as the data source for you Pivot Table.

See http://www.ozgrid.com/Excel/DynamicRanges.htm for some examples

hope this helps.