+ Reply to Thread
Results 1 to 21 of 21

Multi-Line graph to re-size automatically

  1. #1
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Multi-Line graph to re-size automatically

    hi there,

    The attached sheet is just a dummy to work on, from which I will implement what I've learned here onto my actual sheets.

    Two things need to happen here1) The graph must only show the populated months, as the rest of the months are filled in, the graph must resize automatically.
    (2) If cell N1 is changed to a 1, some of the graph data change to zeros, however, the legends are still showing for these fields. So the legends need to also adapt to only show the active fields.

    I have found examples of how to achieve more or less what I am after, although the examples were (are) for 1 column data only, and pie graphs.

    I require a line graph. Any help will honestly be very highly appreciated..
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    instead of making formula =0 make it equal ""

    ie
    n1 =
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    update all the other formulas to be similar to be ""
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    hi, thanx for the reply..I have tried what you suggested, still not the desired outcome. Any other ideas maybe?

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    the simple way would be to put a filter on it and hide all the blank rows
    can that work?

  5. #5
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    That will work, but the problem is that I will have to un-hide the columns as the months go by, so that the chart can plot it. The idea is to have the chart include new months automatically as those months are populated.
    Also the second problem is about the legends..They don't disappear from the graph if they do not have any data to show..

    I guess I could do things the hard (manual) way, but I know there must be solutions to achieving what I am after..Someone out there knows..

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    i tried to do it the easy way first....just in case

    the harder way would be using dynamic named ranges

    its harder to setup but easy to use

  7. #7
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    You are absolutely spot on with the named ranges..I can do all of this that I want with single column data and pie graphs...just cannot get my head around this for multiline graphs...anyway, thanx for being out there and being willing to help..

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    so i did some digging
    this is the closest i saw that comes to what you want with named ranges...you are right...multi series is much harder than normal
    http://peltiertech.com/dynamic-chart...ltiple-series/
    however....it really doesnt do what you are asking

    have you thought about using VBA
    something along the lines of this
    http://peltiertech.com/dynamic-chart-source-data/

  9. #9
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    I really do appreciate your efforts...I have saved the linkes to those websites..just maybe there will be something. Thanx a million!!

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    your code is simpler
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    The macro works perfectly!!..wow!!..Gee man, I donno...will send you some flowers?..hahaha

    You are really a helping hand. Now just to sort out the legend problem!..

    Just to say, I really do not expect you to be put up with this if you don't want to..really...but if you want to, I will be forever thankfull!

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    hahaha
    its not a problem
    ill have a look at it next week when im back at work

  13. #13
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    are you using suggestion in post #2 as well as the macro?

    the "" cells are what determines doesn't get included into the chart data area

  14. #14
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    Good Morning..

    You will see in post #3 I replied that the suggestion was not bearing any fruit...

    uhm...I have actually kind of sorted things out, I have decided on hiding the rows which have a zero value in column A. It does work if I hide the row manually, however to get my code to do it automatically is still a problem. This is what I have, I'm sure it's just a small adjustment somewhere..
    Please Login or Register  to view this content.
    Can you maybe help with this one?

  15. #15
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    i am now confused
    the macro limits the chart range based on blank cells as such changing the legend at the same time
    so make N1 = 1 or 0 then update chart

    anyway if you just want to hide rows..thats much easier
    Please Login or Register  to view this content.
    made it automatic update based on N1 instead of having to run macro
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    Hey man..trust you are well...

    I have made some progress in the meantime, I have come to realize that in order to update the legends on the chart, the best and easiest way is to hide the rows you do not need. Now, there is a slight difference between the dummy chart and what I actually require, which is that I need a macro that will hide rows when a cell in row O is zero. (Scrap N1). So basically if lets say O6 is zero, I would like row 6 to hide, and vice versa. I have found some examples but they are all sluggish to execute. I would like to apologise if I have taken a bit of a detour with you, certainly was not my intention, I actually believed that I would get the answers I needed with the dummy as was set up, but alas, that proved to not be the case. If you feel you have spent too much time helping me, I will fully understand.

  17. #17
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    Hey man..trust you are well...

    I have made some progress in the meantime, I have come to realize that in order to update the legends on the chart, the best and easiest way is to hide the rows you do not need. Now, there is a slight difference between the dummy chart and what I actually require, which is that I need a macro that will hide rows when a cell in row O is zero. (Scrap N1). So basically if lets say O6 is zero, I would like row 6 to hide, and vice versa. I have found some examples but they are all sluggish to execute. I would like to apologise if I have taken a bit of a detour with you, certainly was not my intention, I actually believed that I would get the answers I needed with the dummy as was set up, but alas, that proved to not be the case. If you feel you have spent too much time helping me, I will fully understand.

  18. #18
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    i think you should include a more accurate representation of what you are after

    i can take the latest idea of hiding rows but make it run thru a loop of all the rows and hide the ones which are 0

  19. #19
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    Yes you are right, I should be more specific..Please accept my apology.

    I have found some examples of hiding rows, actually I have also posted a thread on the issue yesterday, but the suggestions are all sluggish to execute..One thing that I cannot have is the calculate event, it has to be a change event which looks at only the range in column O.

  20. #20
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Multi-Line graph to re-size automatically

    do you want/have to do it on change events or do you just want it to be run?

    how do you mean sluggish?
    the screen "flickers?"

  21. #21
    Forum Contributor
    Join Date
    04-16-2014
    Location
    Witbank, SA
    MS-Off Ver
    Excel 2013
    Posts
    268

    Re: Multi-Line graph to re-size automatically

    At Last..Goodness me!!..thank you Olly...It works perfectly. I have moved your code to my spreadsheet, changed the range, and wham!!..it's all there.

    If I could come to the UK now, I would and give you a handshake!!..Thanx for the patience especially..:)

+ 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. Multi Line Graph with series that have inconsistent X axis values
    By potejam in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-08-2013, 07:20 PM
  2. [SOLVED] Change Font Size/Color on one line of an email automatically generated by a code.
    By coxb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2012, 01:55 PM
  3. Replies: 0
    Last Post: 04-13-2011, 06:48 AM
  4. Replies: 1
    Last Post: 06-18-2006, 11:25 AM
  5. multi-line graph with dates on x-axis
    By Ef in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-28-2006, 08:25 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