• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Pivot Chart

K

Karl Spears

New Member
I am looking for a way to dynamically change a chart by selection of filter criteria. There was a post showing how to do this without a pivot table. This worked great. However, in my application the workbook is a something that will be continuously added to. So I dont want it to be a complicated to add the next months data and update formulas etc... Pivot tables and charts make it easy to add more data. The problem I am having with the pivot tables is it always wants to sum the data.

For example if I want to chart sales by month for 3 employees. I would like to select March and April and have the chart graph 2 lines... not the sum of March and April in 1 line. Is this possible in a pivot chart.
 
Can you share your workbook? Short answer is yes, but I'm vexed by this statement "The problem I am having with the pivot tables is it always wants to sum the data."
 
I have attached a simple spreadsheet (Pivot_dynamic_chart) looking at what I am trying to do. I want to filter by year and have the graph show; Y-axis sales, X-axis month. I want to be able to select a year or multiple years and the graph plot a line for sales on each year I select. If I select 1 year at a time it works... but if I select 2 years it sums each month. I want to have a line chart I can turn on and off for each year showing multiple years at a time.

I also attached another file (dynamic-chart-with-check-boxes). I downloaded this example from this website. This does what I want but it is complicated to add new data. Not-so complicated for me as I would create the file but for others adding data to the file. Also, I can think of other uses that would require more frequent addtion of data. The pivot table makes it so easy to add more data to the chart without formulas etc...

Thanks
 

Attachments

  • Pivot_dynamic_chart.xls
    44.5 KB · Views: 15
  • dynamic-chart-with-check-boxes.xls
    62 KB · Views: 12
Hi Karl,

Please go through the attachment. Is it what you want? or not?

Or you want check boxes to view combination of column and line chart as shown in attachment of Chandoo.
 

Attachments

  • dynamic-chart-with-check-boxes.xlsx
    20.4 KB · Views: 22
Getting started with pivot tables, the first step in creating a Pivot Table is to organize your data in a list of rows and columns. Make sure that there is a heading in every column, and no rows that are completely blank.
 
Balli, Thanks for the file. That is close to what I am looking to accomplish. However, investigating the file I cant seem to find how this was accomplished. Did you need to edit the VBA programming to accomplish this?

Thanks again.
 
Hi, Karl Spears!
Since the file extension is .xlsx it doesn't support macros, so there was no VBA code involved. Giving a look at the file neither dynamic named ranges are used, so all the stuff is accomplished thru the pivot table.
Hope it helps.
Regards!
 
@Karl,

As Ninja (Sir) said it is done by using pivot table. What you want to add or change on this, please explain? So that I can do or explain the same.
 
You must log in or register to reply here.
Back
Top