Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Auto-Extending Graph/Chart Range

  1. #1
    Join Date
    14th May 2008
    Posts
    14

    Auto-Extending Graph/Chart Range

    Hi All,

    This is my 2nd post. And I am hoping for positive reply to this one as well.

    I have a Line Graph with 2 lines on it. Is it possible to write a macro to extend the range (Source range) of one of the lines ? I would also like to add a Data Label to the newly added point.

    For e.g. If I have Line 1 graph only till 65, I want the macro to extend the range by one row to include 95 and also display a datalabel

    Line 1 Line 2
    10 20
    20 40
    35 63
    50 85
    65 105
    95 115
    130
    145
    I dont know whether I am trying to do too much with this macro. But if anyone can help me with this, it will be fantastic.

    Thanks in advance

    ManUtd

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th August 2008
    Posts
    53

    Re: Extending A Graph Range

    So,

    If I understand you correctly, you are trying to get a chart to automatically update itself according to how much information has been entered in the columns storing its data.

    For this I would recommend not a macro, but using dynamic named ranges for your series, as per: http://www.ozgrid.com/Excel/DynamicRanges.htm

    Which is shown in my attachment. See how adding and deleting from the bottoms of the columns automatically adjusts the graph to fit.

    DNRs are trickier in charts than in pivot tables but basically, the key is to define the series by dynamic named range, not the chart overall source data by the collection of the series. If someone wants to make that intelligible please go ahead, I'm not precious.

    Finally, if you post an example we might be able to help a little better.

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Cheeky Charlie; August 21st, 2008 at 02:04. Reason: Because I can do it

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,229

    Re: Extending A Graph Range

    See the tutorials available at Peltier Technologies to learn how to create dynamic charts.
    http://peltiertech.com/WordPress/200...ynamic-charts/
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,797

    Re: Auto-Extending Graph/Chart Range

    Lots of ANSWERS TO SIMILAR QUESTIONS directly above your post. A quick search yeilds hundreds more too.

  5. #5
    Join Date
    14th May 2008
    Posts
    14

    Re: Auto-Extending Graph/Chart Range

    Thanks a lot for your help guys. But unfortunately, I dont think your solution answers my questions. Apologies if I failed to explain the problem properly. Charlie, thanks for the dynamic range eg.

    I have attached an example. In the attached sheet there are two lines on the graph for "Cumulative Actual" and "Cumulative Estimated" with Cumulative Actual range only till row 10 (25-05-08). There is already a value in the cell below this one as it has a formula but I do not want to display in the graph now as it the same value (hence the dynamic range might not work). But if you change the value in cell B11 (Actual) the value in cell D11 will be different from D10 and hence now I want to display it on the graph (by extending the range by one row) and if possible also add a data label to the newly added value.

    I am not sure whether this can be achieved using dynamic ranges as I am fairly new to excel. The reason I am trying to automate this is because there are at least 40 such graphs that I need to update.

    Dave, I did search for this one. But could not find an appropriate solution and hence a new thread.

    I hope this explains the problem more precisely.

    Regards,

    ManUtd
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd April 2008
    Location
    Work in North Wales
    Posts
    262

    Re: Auto-Extending Graph/Chart Range

    Cant open yourattachment right now, but a simple way would be to add a helper column to your data which indicates where the change of date is/is not present. Then your dynamic range can use that as the row offset value.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Auto-Extending Graph/Chart Range

    Hi,

    Define the ranges:

    Records

    Refers to: =MATCH(2,1/(Sheet1!$B$2:$B$65536<>0))

    xValues

    Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,Records+1)

    yValues1

    Refers to: =Sheet1!$D$2:INDEX(Sheet1!$D:$D,Records+1)

    yValues2

    =Sheet1!$E$2:INDEX(Sheet1!$E:$E,Records+1)

    HTH

  8. #8
    Join Date
    16th January 2007
    Posts
    570

    Re: Auto-Extending Graph/Chart Range

    hi,Krishnakumar's function is worthy.
    regards/junho lee
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    14th May 2008
    Posts
    14

    Re: Auto-Extending Graph/Chart Range

    Hi Krishna, Thanks a lot for your help. I cant download the attachment as of now. Will try after some time.
    Auto Merged Post Until 24 Hrs Passes;

    Superb Krishna !!!....it works like a treat....thanks a ton .. Now can anyone try and answer the latter half of my question ?...how can I automatically add a data label to the newly added value and get rid of the datalabel of the now second from last value (Basically I want to display datalabel only for the last value)..

    Please remember I want to automate this due to the large number of graphs.
    Auto Merged Post Until 24 Hrs Passes;

    Superb Krishna !!!....it works like a treat....thanks a ton .. Now can anyone try and answer the latter half of my question ?...how can I automatically add a data label to the newly added value and get rid of the datalabel of the now second from last value (Basically I want to display datalabel only for the last value)..

    Please remember I want to automate this due to the large number of graphs.
    Last edited by ManUtd; August 21st, 2008 at 19:00. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th May 2008
    Posts
    14

    Re: Auto-Extending Graph/Chart Range

    Can anyone please tell me whether I can automatically (using a macro or anything) add/delete a datalabel for the last datapoint on the linegraph

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Auto select range for chart series
    By Triggman in forum Excel General
    Replies: 5
    Last Post: April 25th, 2013, 08:07
  2. Chart/Graph Changing/Variable Range
    By JPCreedon in forum Excel General
    Replies: 7
    Last Post: May 31st, 2008, 00:35
  3. Graph/Chart Dynamic Named Range
    By macro blues in forum Excel General
    Replies: 1
    Last Post: May 29th, 2008, 03:37
  4. Auto count added to graph
    By Wattage in forum Excel General
    Replies: 1
    Last Post: October 13th, 2005, 23:47
  5. Formulas: extending graph for new data
    By kimbob8 in forum Excel General
    Replies: 7
    Last Post: September 21st, 2003, 05:22

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
  •  
porno