+ Reply to Thread
Results 1 to 4 of 4

Creating a dynamic chart control

  1. #1
    Forum Contributor
    Join Date
    10-30-2014
    Location
    England
    MS-Off Ver
    2007 / 365
    Posts
    279

    Creating a dynamic chart control

    Example file attached.

    I would like to create a dynamic chart control which allows the user to select certain criteria within cells and the chart will display the results of the selected combination. I know that this is exactly the sort of thing that a pivot table is for, but as this will be used as a dashboard by people other than myself, unfortunately I do not have the time (or patience!) to teach them all how to use pivot tables.

    In the example I have a table of data, a 'Chart control' section and the chart itself. In reality the data is held on it's own sheet and contains many more rows, columns and potential values. The chart itself will actually be the same source information for both the Title and the horizontal axis - just filtered based on the control section - but the vertical axis should be able to change it's source column.

    So the hierarchy will always be...


    Site
    Week No.
    Category x


    The Chart Title will always the be 'Site' column.
    The horizontal axis will always be the 'Week No.' column.
    The vertical axis will always one of the 'Category' columns.
    So I just need to be able to filter which values are totaled within the Title and horizonal axis, but also select which column is totaled within the vertical axis.

    If the 'Site' selector field is blank, I would like this to mean that ALL values are totaled.
    Similarly, if either of the 'Week No.' selector fields are blank then I would like this to mean that the values totaled are the earliest and latest values respectively.
    Also, possibly, if the 'Category' selector field is blank then I could maybe default to totaling the 'Category 0' column.

    Potentially, I may also include a date range selector either in place of, or as an alternative to, the Week No. selector. These would be mutually exclusive so only a Week No. range or Date range would be used at any one time - I imagine I would need to add an additional field to just specify which of the ranges the chart should use.

    Finally, please ignore the totals I have used on the example chart, they are just placeholders and not the actual totals I would expect.

    Any help on the best approach to this would be appreciated, this is normally something I would take the time to sit down and puzzle out myself, but timescales are against me on this one.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Creating a dynamic chart control

    Give this a shot. It uses pivot tables, only the user doesn't have to know that. It also uses change events and cascading dropdown lists. The Pivot Sheet can be hidden.

    I made some changes to the spreadsheet so I could test it. I made the data an excel chart so I could add data to it. I created a site F so I could test some of my filters.

    When you select a site, it sets the filters on several pivot tables that control both the dropdown lists and the charts. These are overlaid with named dynamic ranges that are used as the data source for the dropdown lists. I wound up using "(All)" as a pivot table header so it could also be set as a pivot table filter. Setting either of the week numbers or dates to (All) will get all dates based on the other selections. There's a bit of a two-step in the code that sets the filters that allows this.

    Note that the chart itself is plotted off two named dynamic ranges Plot_Category and Plot_Week that overlay PT_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
    Forum Contributor
    Join Date
    10-30-2014
    Location
    England
    MS-Off Ver
    2007 / 365
    Posts
    279

    Re: Creating a dynamic chart control

    Thank you for taking the time to look at this.

    I had a feeling that some kind of pivot manipulation might be one of the solutions offered, unfortunately, the code written is currently a bit above my level of understanding. I think if I used this approach for this particular project I would get myself into trouble when trying to maintain and update the file going forward. However, I may take this into another project on the horizon, where I should have more time and be able to learn from your solution.

    I actually had some unexpected time granted to me earlier today, so I was able to try and puzzle out how I might accomplish the desired results in the easiest way. I believe I have a solution - although I still need to actually implement it - which involves building a separate source table for the chart with a row per Week No. and a second dynamic column for (may use MATCH) the correct Category column for use in a SUMIFS - SUMIFS(DynamicColumnToSum , SiteColumn,Site , WeekNoColumn,WeekNo). I think I may also have to use INDIRECT, which I didn't really want to as it's volatile, but I think it should only be one use of it per Week No., so only 52 in total.

    I'll post my final solution when I get it all built just in case it helps anyone else.

    Meantime, any other thoughts or ideas always welcome.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Creating a dynamic chart control

    Here are two links that might help you out. Named Dynamic Ranges can keep you from using Indirect.

    http://www.utteraccess.com/wiki/inde...Dynamic_Ranges

    This link explains how to use named dynamic ranges in charts.

    http://www.utteraccess.com/wiki/inde...namic_Charting

    This is about 50% of the spreadsheet I sent you. Using Match to find an offset to the range you want to plot is a very powerful tool.

    Good luck with your project.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating a Dynamic Chart
    By Tulsip in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 01-20-2016, 02:37 AM
  2. [SOLVED] Creating a dynamic area chart
    By VBANovice46 in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 09-03-2015, 05:40 PM
  3. Creating dynamic userform based on several file names and creating chart overlays
    By Thorn23 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-18-2014, 12:09 PM
  4. Dynamic Pie Chart with Control Box....help!
    By crazymonkey in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 02-14-2014, 12:17 PM
  5. [SOLVED] Dynamic X-Axis in excel chart using form control
    By ejdrouil in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-16-2013, 09:56 AM
  6. Replies: 0
    Last Post: 03-18-2013, 08:43 PM
  7. [SOLVED] Dynamic control chart
    By aka1 in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 08-08-2012, 02:45 PM

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