Dynamic Chart Type when Using a Multiple Consolidated Range Pivot Table

[HangMan]

I have a PivotTable constructed using Multiple Consolidated Ranges (basic sample attached).

Is there a way to dynamically (or otherwise) change the 'Chart Type' based on the 'Report Filter'? In the attached sample, I would like the Chart Type to be a 'Column Chart' when the 'Page 2' Report Filter says 'Monthly' but for the 'Chart Type' to automatically change to a line chart when the 'Page 2' Report Filter is changed to 'Cumulative'. Note: This will be Excel 2010 distribution.

I will be using Pivot Slicers in the finished version and the actual graph will appear on a seperate worksheet to the data, so te user will only see the chart and the slicers (not the source data) but I assume the same logic applies? I'm assuming this would have to be done using VBA, but I have no ideas how this could be achieved?

There will ultimately be four Report Filters, but for now the Chart Type will only need to be affected by one of the four.

Many thanks

[JohnTopley]

I have little (no!) knowledge of Pivot Tables but by trial and error I managed to get the following to change chart types.

What I don't know is whether the range defined as target ($B$41:$E$59) is constant or will vary with your Pivot tables. This is the range which is "invoked" when choosing your filter (C42)

This code needs to be inserted on the "Dynamic Chart Type" sheet.

Right click on the sheet tab, "view code" and insert the code below.

Please Login or Register  to view this content.

[HangMan]

Hi John,

Well, I'm very impressed, that actually works really well. I tried adding in

Please Login or Register  to view this content.

to see if I could stop the 'jump' between the chart type changes but it seems to make no difference. It lloks as though it is effectively unfiltering then filtering again or something, not sure, but I guess that is an Excel thing.

I think the range defined as target ($B$41:$E$59) should remain constant, I can't see any need for it to change at the moment (though I may eat my word later)...

I'll test a little more, but this looks like a perfect solution...

Many thanks

[JohnTopley]

I "tested" on the assumption that the choice would be "Cumulative" or "Monthly": I did not check the "All" option if this likely (showing my ignorance!).

If you are happy with the solution, can you mark the thread as SOLVED.

Thank you.

[HangMan]

Logically it can't be 'All', though of course physically it can be as in the user can disable the filter. I don't think it is an issue though...

Many thanks

[HangMan]

Many thanks once again :-)

[GEGoodwill]

Hi All,

Please find the excel file in which you can filter your result in pivot table according to dropdown selection and find the code give below:

Code:

[HangMan]

Hi GEGoodwill,

I think maybe you forgot to include your code?