No XY PivotChart type?!?!? Work-Around??

  • Thread starter Thread starter d-cubed
  • Start date Start date
D

d-cubed

Excel 2000 does not support the XY PivotChart type. As a
work-around, I make a dummy chart and copy data into it
from a PivotTable. The down-side to this is the extra work
and the lack of dynamic linkage between data and chart
(the chart always refers to fixed cells, not the slected
parameters).

Is there a more effective work-around?

d-cubed
 
Hi D-cubed,
Excel 2000 does not support the XY PivotChart type. As a
work-around, I make a dummy chart and copy data into it
from a PivotTable. The down-side to this is the extra work
and the lack of dynamic linkage between data and chart
(the chart always refers to fixed cells, not the slected
parameters).

Is there a more effective work-around?
Click to expand...

Not that I know of, sorry. The only thing that can make it less
onerous is to automate the chart production using VBA (i.e. in response
to changing the pivot table), but that is not a trivial task.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Create a named formula that refers to the PT data range (I imagine you
don't have sub-totals). Now, when the PT is refreshed and the data
change, the chart will adjust itself. For more on using a name in a
chart, see the 'Dynamic Charts' page of my web site. You will find
links on creating a named formula and on using one in a chart.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Actually, individual series based on named formulas will update as the
range resizes, but using one named formula for the entire chart's source
data range doesn't, because Excel converts the named formula to the
absolute address of the range.

That said, you could still define a named formula based on the pivot
table, and write a relatively simple macro to reassign the chart's
source data range to the updated formula.

If the top left cell of a "simple" pivot table is located in Sheet2 cell
A1, you could define a formula (press Ctrl-F3) named "PT" which refers to

=OFFSET(Sheet2!$A$1,2,1,COUNTA(Sheet2!$B:$B)-2,COUNTA(Sheet2!$2:$2)-1)

The a simple macro like this will update the chart:

Sub UpdateChart()
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("PT")
End Sub

What complicates it is updating all the series names and category
labels. And accounting for any crazy changes if the structure of the
pivot table changed dramatically.

- Jon
 
Back
Top