Combination Pivot Charts

[Catenary]

Using Excel 2007, I have created a pivot chart with two data sources shown in column form and one in line form (plotted on the right, secondary axis). When I change the source data for the line component, however the chart reverts to a simple column chart, losing the secondary axis in the process. Is there a way to keep the combination chart format without manually changing the third data set to line chart each time?

DETAILS

The columns represent volumes of water pumped from two extraction wells and have values in the 10^5 gallons per week range. The line represents the water elevation in a specific nearby monitoring well and has a magnitude in tens of feet. I have to assess the effect of the historical pumping on 35 individual wells. Ideally, I would click a button to select "WELL 26" and the line representing its fluctuating water level would be overlaid on the column chart showing weekly pumping volumes. As it works now, I have to manually reinstall the secondary axis and convert the column chart type to a line chart each time I select a new monitoring well.

With some (considerable) effort, I suppose I could create a macro to do this, but I'd prefer not to, if at all possible.

Thanks for any assistance this request might engender.

 

[BiocideJ]

I didn't think this was a problem with Excel 2007 (only prior versions)
Could you perhaps just select more blank cells in the column i.e. if your range of actual values is C4:C100, set the pivot table to read from C4:C20000. It shouldn't plot the blanks so you shouldn't need to reset the source data.

If that doesn't work, I'm not sure a macro would be that difficult as it you could just record your actions since the chart and components don't actually change.

 

[AlphaFrog]

This wont keep the combination chart format, but may make it easier to reset back to your combination chart format.

Reuse a favorite chart by using a chart template