Results 1 to 8 of 8

Thread: Excel graphing

  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Johannesburg, South Africa
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a situation where I have many graph sets, each set of graphs consists of three graphs where the first graph in the set spans an X-Y range from A1:Z1 and A2:Z2 say (ie the full range), the second graph range grows each week by one column ie in week 10 the X-Y range will be A1:J1 and A3:J3 in week 11 will be A1:K1 and A3:K3 etc. The third graph range in the set shrinks in a similar way by one column each week.

    All the graphs share the same column that the range ends in, ie. I could have 30 graph sets where the second graph end in column J this week and column K next. The graph row data do not change from week to week.

    Currently I laboriously have to open each graph and manually change the x and y values of each range for each second graph in each graph set to the new weeks ending column. Is there a way in which I can enter the range ending column once (in a cell say) and have the ranges updated based on the value I input into that cell?

    Am at my wit's end with this one and would appreciate whatever assistance I can get.

    Thank you

    Theo

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Theo,

    Attached is a worksheet which I believe solves your problem. It does so via the use of the OFFSET function and NAMED RANGES.
    The down side is you have to create two names ranges for each chart one for the X axis and one for the Y Axis. But once done the ranges will automatically adjust as data is added/deleted and the chart changes with it.

    Let me know if this is what you were looking for.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Johannesburg, South Africa
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there

    Thank you for your prompt response. It is certainly appreciated and I don't believe the effort one would have to put in is a barrier to using that solution, but unfortunately the graphs I need this for pose a challenge I had not foreseen before seeing your proposed solution. As I am a first time user of the lounge forum (but long time full subscriber to Langalist, Gizmo, Windowssecrets etc) I was not aware that one could upload example files. Thus I am going to try that and annotate accordingly to try and clarify my dilemma.

    Regards

    Theo
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Theo,

    This entire process, including the import of the data and elimination of zeros that should be blanks by input of an end date, could be automated via the use of VBA {Visual Basic for Applications}. This is however far beyond what can be accomplished on this forum. My suggestion is that you find a consultant that does this kind of work, it will be well worth the money you spend by the time savings and increase in accuracy of the operations.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey Theo,
    I think I have understood your predicament, and hope I have an somewhat easy fix

    Rather than write some extravagant code for this, I have simply used a dynamic range name for the actual data, and added some buttons which will change it up and down as you wish....if that makes sense. All you will have to do when you copy and paste the new data, is to hit the button to add data. For your ease, I also added a button to automatically reduce the data by one wk as well, just in case you hit the increase twice, or just wanna play with it

    These changes can be found using the INSERT - NAME - DEFINE menu items for the dynamic range names, and within the source data area of the chart itself

    The VBA code comoponent (ALT + F11) is really simple (and short) and references cell range A5 (for the purpose of the exercise only, and can be changed to any other cell as needed)

    The only real problem will be setting it up for all of the graphs, but once done, clicking the button will change all graphs at the same time

    Hope this helps you out, or gives you a better idea

    [attachment=89265:Example graph (dynamic data range test).xls]
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Johannesburg, South Africa
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there to both RetiredGeek and dabbler68. Thank you for your valuable input, I have tried out some of the additional information I gleaned from dabbler68 and have tested it. What I need to do now works and all that is left now is the lengthy once off exercise of setting up the spreadsheets. Thanks again, learnt a lot, and the VBA script put a nice touch to it, although I would have even been happy to just type in the width every time.

    Great stuff and really worth it for me.

    Theo

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a method without VB...

    Create the names (insert name - define)
    xDate =Sheet1!$B$2:$S$2
    yPlanned =Sheet1!$B$3:$S$3
    yActual =OFFSET(Sheet1!$B$4,0,0,1,MATCH(0,Sheet1!$C$4:$T$4 ,0))

    Then assign those names to the chart.
    [Put a zero in the cell T4 in case the range is filled, this will be the first zero.]

    The offset named range starts at B4 and extends to the first 0 in the range C4:T4

    Steve
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Now this is exactly why I read this newsletter and follow lounge postings... I have a similar situation, and knew there had to be a way to improve on all the manual effort. Thanks people. It will take some coding to make the changes but this discussion is going to save me at least half an hour every day once I finish the updates.

Posting Permissions

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