+ Reply to Thread
Results 1 to 9 of 9

Dynamic x-axis chart

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    51

    Dynamic x-axis chart

    Hey guys, I have a graph that represents a monthly average of scores that we collect from customers and I need to be able to show that graph with dynamic starting and ending dates. I have attached a copy of the spreadsheet to look at.

    The cells in blue should be the cells that the user changes to display what time span they want to look at. I created the Update Graph button but havn't built anything behind it. If you guys could give me a hand that would be much appreciated. Thanks!

    Example.xlsx
    KCCO

  2. #2
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Dynamic x-axis chart

    G'day Neon,

    My example I've applied to your example attachment is from the great Andy Pope.

    Click here for Andy's instructions.
    Attached Files Attached Files
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Dynamic x-axis chart

    Hi NeoFlex,

    See the attached workbook where I have used define names for chart data sources

    Example(41).xlsx

    press ctrl + F3 to see define names and their formulas. thx

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Dynamic x-axis chart

    Hey dilipandey, that was exactly what i was looking for. However, after testing it out for a bit I noticed one error I couldnt figure out. When setting the date range, if I set the End Date then set the Start Date it will push the graph axis further into the future than what is specified. I think it has to do with the Offset in the Define Names area but I couldnt figure it out when i looked. Any help would be greatly appreciated again, thanks!
    Attached Files Attached Files

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Dynamic x-axis chart

    Okay.. thx for the heads up.

    Defined name formula revised as :-
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    should work now, see attached:-
    Example(1).xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>

  6. #6
    Registered User
    Join Date
    10-01-2012
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Dynamic x-axis chart

    Hey dilipandey, so that didnt exactly fix the problem I was having. If you start with the dates as START: 12/31/12 & END: 4/30/13 .....then....change the start date to START: 1/31/12 it will push the end date forward one month to 5/31/12. I think it just has to do with when the OFFSET calculates what numbers its going to pull. However, if you can somehow get that fixed that would be GREAT!

    BUT....

    Another issue I have, and a little more important than the graph one is making the date 'define name' range dynamic. Right now it is harcoded as $K$8:$K$18 however, that grey table to the right will expand as time goes on so I need it to catch all numbers after K8...so something like $K$8:$K$COUNTA($K$8:$K$100) but maybe something a bit neater and written better.

    Thanks again for all your help you've been, I really do appreciate it.

    (I reposted an updated spreadsheet)
    Attached Files Attached Files

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Dynamic x-axis chart

    Okay.. now see the revised attachment

    Example(2).xlsx


    Regards,
    DILIPandey
    <click on below * if this helps>

  8. #8
    Registered User
    Join Date
    10-01-2012
    Location
    Indianapolis, Indiana
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Dynamic x-axis chart

    That's perfect. Thanks for ALL your help dilipandey!

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Dynamic x-axis chart

    you are welcome NeoFlex... cheers



    Regards,
    DILIPandey
    <click on below * if this helps>

+ 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