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

    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

    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

    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.

    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

    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.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]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.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]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.

    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

    Re: Auto-Extending Graph/Chart Range


    Create another series based on a range that has a formula.
    The formula should output #N/A for all but the cell that contains the last point.


    Apply data labels to the series. Only the valid points, which is 1, will display the label.

    [h4]Cheers
    Andy    [/h4]

    Re: Auto-Extending Graph/Chart Range


    Very neat Andy, I was pondering code - but not necessary with your solution.


    FYI ManU, you can force an NA error with =NA() which can also be put into ifs/lookups etc.

    Re: Auto-Extending Graph/Chart Range


    Thats great you guys....I am sure that will work...I will try that right away.. n hopefully I should trouble you guys more with this issue :)


    Many thanks again...it is really appreciated...[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]BRILLIANT !! Andy n Charlie....it does work...I have implemented it...used the following formula for the new range


    =IF(NOT(B2=0),IF(B3=0,D2,NA()),NA())


    This website really rocks !!!![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]BRILLIANT !! Andy n Charlie....it does work...I have implemented it...used the following formula for the new range


    =IF(NOT(B2=0),IF(B3=0,D2,NA()),NA())


    This website really rocks !!!!

    Re: Auto-Extending Graph/Chart Range


    Quote

    BRILLIANT !! Andy n Charlie....it does work...I have implemented it...used the following formula for the new range


    =IF(NOT(B2=0),IF(B3=0,D2,NA()),NA())


    This website really rocks !!!!


    Please post the final version of your workbook so that others who search and find this post will have an example. Thanks!

    Re: Auto-Extending Graph/Chart Range


    Thanks for following up ManU - this really helps the forum to work best. As a general tip, you can really help yourself for the future by regularly returning to the "logic" of what you're trying to do.
    I'll work through your 'labels' column to explain:
    =IF(NOT(B2=0),IF(B3=0,D2,NA()),NA())


    1. IF(NOT(x=y)) means the same as IF(x<>y) (<> means not equal to). Obviously, the second is easier to read. So we could write:
    =IF(B2<>0,IF(B3=0,D2,NA()),NA())


    2. Using an if with parameters for "if true" and "if false" means logical statements can be inverted providing the "results" are swapped, like this:
    =IF(B2=0,NA(),IF(B3=0,D2,NA()))


    3. Nested ifs can sometimes be "compounded" with AND and OR statements (which work like NOT, i.e. applied 'around' other logical statement(s)). So let's look at the logic; you are testing two criteria:
    "Don't make a label if B shows 0 (i.e. beyond end of data range)" then -
    "If the next row value in B is 0, show the cumulative value (D) for this row, i.e. show the label if it is the end of the data"
    Or, getting nearer to the "boolean" form: show the data if it is not beyond the end of the range but the next value is:
    =IF(AND(B2<>0,B3=0),D2,NA())


    Which I think is easier to read and understand later on.


    HTH

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login