+ Reply to Thread
Results 1 to 19 of 19

Dynamic Chart on both X and Y axis

  1. #1
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Dynamic Chart on both X and Y axis

    Hello,

    I want to make a dynamic chart that can grow or shrink both on the X and Y axis. In the table the months are put horizontally and the different departments are put in the different rows vertically. Which way do you think its the best to make the name ranges so it will work? Should i make them for each month vertically or for each department horizontal?

    PS: The table can change from 1 row 1 column to 13 columns 21 rows, its controlled with drop down menus.

  2. #2
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    if i make the name ranges horizontally for each department the chart will work great only if you add or remove months. It will shrink and grow automatically. But if i remove or add a department the chart will remove the data but it will leave blank space in the place of the removed data.

    The same thing will happen if i do it vertically for each month. If i add or remove department the chart will work ok but if i add or remove a month it wont remove the blank space.

    I know it doesnt seem as a problem but if i remove 20 departments or 11 month the chart will be huge for the small information it will show.

    Is there a way to import both vertical and horizontal name ranges in the chart? Or is there any other way to achieve this?

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,031

    Re: Dynamic Chart on both X and Y axis

    Without any details (what you are really trying to show, where the data is coming from, etc.) it is difficult to make any recommendations.

    My first thought is to organize the data in a good database format, then use a pivot table and pivot chart to summarize and display the data. This assumes that what you are trying to do "fits" what a pivot table/chart can do. As it applies to your question, pivot charts are nice because they are very dynamic -- they respond to changes/filters/etc. in the pivot table automatically.

    Without more detail, that is going to be my first recommendation. Look at what you are doing and see if it fits into something that a pivot table/chart can accomplish.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Dynamic Chart on both X and Y axis

    Can you upload sample file? Without it, I'd be taking a guess as to what sort of chart you've set up and how you defined named range etc.

    To upload, go to "Go Advanced" and use Manage Attachments menu (short cut button isn't working at the moment).
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  5. #5
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    im uploading a very simplistic sample of what im doing but its the same idea. The name ranges here are horizontal. if i change the number in column I the name range will grow or shrink and the chart will change depending on it. But if i remove a continent it wont completely be removed from the chart (the black space will still be there). Please add a comment if you know how to make this work in anyway. A pivot chart work for me.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,031

    Re: Dynamic Chart on both X and Y axis

    Your result looks like a pivot table, so I would pursue a pivot table/chart solution.

    0) Start with data in a good database arrangement:
    Please Login or Register  to view this content.
    0a) If you don't have access to the original database, I understand that tools like Power Pivot have an "unpivot" command that can convert a table like in your example to a database list like I suggest.
    1) Insert pivot table with month/date as row labels and continent as column labels and sum of values as the value field
    1a) If your month/date column contains real Excel dates, then you may need to use the Group command to group multiple dates during the same month into months.
    2) Insert column chart based on pivot table and format accordingly. http://www.excel-easy.com/data-analy...ot-tables.html

  7. #7
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    so what i want cant be done? pivot table charts wont work for what im doing.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,031

    Re: Dynamic Chart on both X and Y axis

    I did not say it cannot be done, I am optimistic that most anything can be done with enough time, energy, and ingenuity. I just said that, based on the example you gave, a pivot table seemed the easiest way to accomplish what you show in your example. A pivot table approach seems so much easier to me than other dynamic named range approaches, that I would ask back. What is different about your real data (that your sample does not show) that a pivot table will not work?

  9. #9
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    I have a 1000 rows database with 20-30 columns of different information for each department. With the help of the dropdown menu i choose which department for which month and year to be compared to which other department. I have made everything else to work. The table that is build with the dropdown menu works perfect. Now i just need to make a chart for this table that can change its size from 1 row 1 column to 21 row 13 columns. Please if you know how help me do it the way im asking thank you

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Dynamic Chart on both X and Y axis

    In general, as MrShorty has indicated, it is a good idea to start from flat table structure for any sort of analysis and use PivotTables and other available tools.

    With newer version of Excel, there are many tools available that simplify dynamic reporting along with more powerful analytics (Get & Transform comes standard now).
    And PivotTable is one of the best tools out there for summarizing data.

    Also as MrShorty wrote, most things are possible in Excel, difference is in which method offers more/less obstacle in achieving the end goal.

    From the looks of it, what you are trying to achieve will need VBA, as without it, SERIES formula for removed groups cannot be removed from chart.

  11. #11
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    how hard will the VBA code be? is anyone more familiar with VBA and able to help?

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,031

    Re: Dynamic Chart on both X and Y axis

    I am still not seeing why a pivot table will not work.
    With the help of the dropdown menu i choose which department for which month and year to be compared to which other department.
    Pivot tables include built in filter dropdowns. If you design your pivot table correctly, it should still be simple dropdowns to choose department and month/year for comparison.

    I'm sure we can help you with the VBA, if that's what you really want to do. However, it still seems more difficult to me to use VBA than to use a pivot table/chart for this.

  13. #13
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    i will be extremely grateful if you help me with the VBA. I havent seen anything like it on the internet. I have zero experience with VBA so it will be really hard for me to do on my own

  14. #14
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Dynamic Chart on both X and Y axis

    I'll have to refresh myself on chart object model for VBA. Give me sometime.

    I used to manipulate charts using VBA, but then I have since moved most of my reporting to PowerBI (using R script and other tools it's very powerful).

    In the meantime here's demo of what can be done using PivotTable using slicer controls.

    I transformed your data using Get & Transform into flat table structure, then based pivot table off of it. I put the table back on the sheet for demo.
    But I'd recommend loading it directly to data model and create pivot table from data model.
    Attached Files Attached Files
    Last edited by CK76; 08-24-2017 at 02:09 PM.

  15. #15
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    I like what you have done but i will have to make a really big table. Since there are 3 years 12 months 21 departments and 20 colums for different type of data for each of the departments. Also with my dropdown menu i have different type of comparision and i dont think it will be easy to create a different row for each one. Also the dropdown menu has a better user interface . So again if anyone can help with the VBA thank you

  16. #16
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Dynamic Chart on both X and Y axis

    I hope you are ready to customize VBA. Since I can only write based on your sample.

    I'd recommend uploading workbook that accurately replicate your workbook set up. From the what you wrote, sample file is simplified version that lacks any dropdown that controls your chart.

    In simplest form, following VBA will change chart source range.

    Please Login or Register  to view this content.
    See attached Sheet2.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    thank you for the file! I prefer to send it via email instead of uploading it. If you want i can PM you.

  18. #18
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    Can anyone who knows VBA do the same thing as CK76 in the file he uploaded in sheet 2 but for the table in this post.

    ps: i know the placement of the table is weird and that the chart has too much information but please anyone who can write in VBA and understands the code of CK76 it will be extremely helpful.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    08-17-2017
    Location
    Sofiya
    MS-Off Ver
    2016
    Posts
    44

    Re: Dynamic Chart on both X and Y axis

    anyone ?

+ 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. Dynamic Axis in chart
    By musicbox1970 in forum Excel General
    Replies: 3
    Last Post: 03-03-2017, 07:02 AM
  2. [SOLVED] Dynamic x-axis chart
    By NeoFlex in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 04-02-2013, 09:50 AM
  3. Dynamic chart: problem with the x-axis
    By moraei in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 12-07-2012, 12:54 PM
  4. Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)
    By TEBrown in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 06-21-2012, 10:15 AM
  5. Dynamic X Axis in line chart
    By craigproudfoot in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 11-02-2009, 12:43 PM
  6. chart with dynamic x axis
    By name in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-04-2006, 10:45 AM
  7. Chart with dynamic x-axis names
    By Jon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2005, 10:06 AM

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