+ Reply to Thread
Results 1 to 4 of 4

Creating Dynamic Charts

  1. #1
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Excel 2013 x64
    Posts
    634

    Creating Dynamic Charts

    I'm reading up on what I can regarding dynamic dashboards and charts. And while i have got the basics of VBA down, I just can't seem to understand where to start in creating these dynamic charts.

    I have attached some sample data that shows Revenues for 6 different departments for 49 different locations. This data is actually pulled from an online source where I need to set what I want to look at via a pivot table. The attached is a copy of that pivot data.

    The idea here is to have a drop down or other selections where the user can view a clustered column chart. The user could either chose a specific location or region (which groups X number of locations) and then also be able to view the information either by Year, Quarter or Month/Period.

    Note, I can't actually show which locations go to each region. The pivot data i receive doesn't do that way. so its either choose All properties, and then a location. Or chose a region and no actual location.

    Ideally, I would love it if possible to show a comparative of this year,quarter, period vs last year, quarter, period. But not sure if the data is currently structured in a way to do that.

    I've never been to strong with dynamic charts and learning this part as I go along.

    If any has any suggestions, good tutorials, etc as well, I welcome them in trying to learn and create this.

    Thanks
    PT
    Attached Files Attached Files
    Last edited by ptmuldoon; 01-11-2019 at 04:48 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,190

    Re: Creating Dynamic Charts

    I am not exactly sure what you want, but here is a thought to get started.

    I converted the data into an Excel Table (because I intend to use slicers) and I removed the total column and the total row. I highlighted the whole thing and made a totally incomprehensible chart ... until you you start filtering things out which I do with slicers. Do a web search on slicers, there are dozens of tutorials out there, but they are intuitive and very powerful. Selecting one slicer limits what you can select on other slicers. In the example I am looking at the first half of 2015.

    You can cut / paste the chart and the slicers onto their own sheet so you can expand the chart.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-28-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Creating Dynamic Charts

    Hi Ptmuldoon, you may have already solved your problem since you posted your request a while ago, but attached is a rough example of slicers using your data. Take a look and let us know if this was what you were looking for.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Excel 2013 x64
    Posts
    634

    Re: Creating Dynamic Charts

    Thanks for the help and tips guys.

    I've been trying to stay away from slicer and using the pivot table to create the dynamic charts. Because this data comes from an line source, I can also create various CUBE formula's. Thus, i've started creating some custom data sets and using various SUMPRODUCT and IndexMatch in pulling out and creating some dynamic charts based on the user input a scrollbar form controls.


    I've been modeling off of some pretty cool looking dynamic dashboards out there on the web, So far its working pretty well and when I get to a decent point, I can post back a sample.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1