Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Auto-Extending Graph/Chart Range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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
    Last edited by Cheeky Charlie; August 21st, 2008, 02:04. Reason: Because I can do it

    Comment


    • #3
      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

      Comment


      • #4
        Re: Auto-Extending Graph/Chart Range

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

        Comment


        • #5
          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

          Comment


          • #6
            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.

            Comment


            • #7
              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
              Kris

              ExcelFox

              Comment


              • #8
                Re: Auto-Extending Graph/Chart Range

                hi,Krishnakumar's function is worthy.
                regards/junho lee
                Attached Files

                Comment


                • #9
                  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, 19:00. Reason: Auto Merged Doublepost

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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.

                      Cheers
                      Andy

                      Comment


                      • #12
                        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.

                        Comment


                        • #13
                          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...
                          Auto Merged Post Until 24 Hrs Passes;

                          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 !!!!
                          Auto Merged Post Until 24 Hrs Passes;

                          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 !!!!
                          Last edited by ManUtd; August 23rd, 2008, 00:36. Reason: Auto Merged Doublepost

                          Comment


                          • #14
                            Re: Auto-Extending Graph/Chart Range

                            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!
                            AAE
                            ----------------------------------------------------

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

                            Comment


                            • #15
                              Re: Auto-Extending Graph/Chart Range

                              AND read the rules on asking questions MOT covered by your thread title if you wish to get more help in future.

                              PM me when you have done so.

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X