Solved

Automatic Chart Updating

Posted on 2011-09-21
13
269 Views
Last Modified: 2012-05-12
I am trying to insert a new line of data in a defined chart area called "CompletionByTheArea".  "CompletionByTheArea" is a defined name on Sheet1.

Problem is that when I insert a row, expanding the range by one, the chart does not see the new row, even though the new inserted row is contained inside of the defined "CompletionByTheArea" range.  

The only way I get the new row of data into the chart is to do another chart>select data and type over the chart data range each time, ='Sheet1'!CompletionbytheArea   Excel macro recorder did not do this.

Have I have a chart named Chart1.

How in VBA would I redefine the chart range so that it updates the chart, and includes my new row? without having to redefine it everytime?  -R-

0
Comment
Question by:RWayneH
  • 8
  • 4
13 Comments
 
LVL 2

Expert Comment

by:jan24
ID: 36576851
I'm working on the assumption that the chart is within a worksheet (Sheet1, say) and you only have one chart on that worksheet.  I've also assumed that your named range is a normal global name.  If any of that doesn't hold then send over your Workbook and I'll have a look at it.

You need to put the following code in the VBA for that sheet (right-click on the tab and do View Code).

Private Sub Worksheet_Change(ByVal Target As Range)
  Me.ChartObjects(1).Chart.SetSourceData Source:=ThisWorkbook.Names("CompletionByTheArea").RefersToRange
End Sub

Open in new window


You can vary this by refering to the chart by its name Me.ChartObjects("Chart 1") perhaps assigning your own name to it by typing Me.ChartObjects(1).Name="Fred" in the Immediate window in the VBA editor.  If you find the code is slowing things down at all, you could also use a different event, e.g. the Calculate event, or use Target to check whether the change has actually impacted you chart.  
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36577007
Hello,

a chart will update if a row of data is added or removed to the source table. What exactly is the definition of the range name, and what is the definition of the chart series?

This should not require VBA. In fact, if the chart already uses the range name as the data source, then the macro above will achieve nothing. But you must make sure that range name and chart source are properly set up. Can you post a sample file that illustrates the problem?

cheers, teylyn
0
 

Author Comment

by:RWayneH
ID: 36577965
0
 

Author Comment

by:RWayneH
ID: 36577985
Sorry sent file w/out explaination.  I click on row 4 of the data file and insert a row, which expands both range areas. In this example ChartOneArea and ChartTwoArea.  After inserting the row and populating the data, I have to manually redefine the chart range to reset the chart and show the new row in the chart.

What I am looking for is a way to automate that with VBA..
0
 

Author Comment

by:RWayneH
ID: 36578012
This may sound like a wierd request too, but is there a way to get the newest data in the the graph left-to-right? instead of right-to-left?  I minor detail but does make a difference to me.  Thanks.  -R-
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36578081
Hello,

>>  I click on row 4 of the data file and insert a row, which expands both range areas.

With this, you are actually inserting a row ABOVE the chart range, not extending the chart range. Despite the fact that you have used a range name and entered that into the Data Range box, the actual series definitions override that. Look at the series formulas and see where they start. Insert a row and look again. The series definition has moved down one row.

But you don't need VBA to fix that. You could use dynamic range names to define the chart source and plug these range names into the series definition.

In the attached file, I've defined the following range names to populate chart one:

cht1Labels      =OFFSET(ChartData!$A$3,1,0,COUNT(ChartData!$A:$A),1)
cht1Series1      =OFFSET(cht1Labels,0,1)
cht1Series2      =OFFSET(cht1Labels,0,2)
cht1Series3      =OFFSET(cht1Labels,0,3)
cht1Series4      =OFFSET(cht1Labels,0,4)

I've left chart two unchanged.

If you want to change the order of the X axis, you can format the Axis with the setting "Dates in reverse order" on the Scale tab. But that will push the Y axis to the right hand side.

As an alternative, you can specify the axis as a Category axis instead of using the default, which will be Time. Click the chart and then Chart > Chart Options > Axes and tick Category. Now you can sort the source table ascending or descending and the chart will reflect that order.

cheers, teylyn



ChartExample.xls
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:RWayneH
ID: 36578114
Can we do this with VBA?  My macro inserts a row at row 4.  When I search the named range it does increase, just not the chart ranges.  Is there a way to not have the absolute values in the Data range? and just give it a name?

Not understanding the cht1Label cht1Series1 ?  where are  those named ranges?  I hit the dropdown kittycorner cell A1 and none are list in that?

Guess I am not understanding this. -R-
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36578218
Hello,

dynamic range names don't show in that kittycorner. Click Insert > Name > Define to see these range names.

The problem lies in how the chart is created. As I said above, Data Range box for the whole range will work fine when the chart is first populated, but the series formulas derived from that will not use that range. They will use individual ranges which start one row below the title row, i.e. row 4. If you insert a new row below the title row, then the series definition will start in row 5.

If you want to work with range names to define the chart source, you need to define the range names at the series level. Range names don't work with the Data Range.

Once the range names have been defined and plugged into the chart, the macro can create as many rows as you want, above the data, below the data,  doesn't really matter. The range will grow and since the series formula refers to that range directly, the chart will update.

cheers, teylyn
0
 

Author Comment

by:RWayneH
ID: 36580356
Where are the names in xl2007?  It looks different then in xl2003.  I understand that naming the Series ranges so each time a row in inserted that those expand too, but not sure that it will solve the issue of re-defining the chart data range.

Still confused.. and trying to understand.  Do you know of any screen captures or videos that would show how to set this up in xl2007? -R-
0
 

Author Comment

by:RWayneH
ID: 36580370
0
 

Author Comment

by:RWayneH
ID: 36581199
Not following this!  Need more of a step-by-step then how to create this kind of Chart, if I can not redefine the Chart data range.  HELP!  -R-
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 36583706
Since you posted an XLS file above, I assumed you are using 2003. It would help to let us know what version you are using, because there are differences.

In 2007 or later, you can see the dynamic range names in the Name Manager.  Formulas Ribbon > Name Manager

Select a range name, then click the formula in the Refers To box and you will see the marching ants highlight the data that the formula returns. To apply a range name to a series, select the data source and edit the series. In the Series values box enter the range name with the reference to the sheet or to the file, e.g.

=Sheet1!MyRange

or

=Book1.xlsx!MyRange

You have to precede the range name with the sheet or file name, otherwise it will not be accepted.

A few things:
- Although the "Chart data range" does accept a range name when you define a chart, it will convert that range name into a direct reference. When the definition of the range name changes, this does not affect the chart at all, because it no longer refers to the named range.

- When the chart is created, the Series formulas will be derived from the chart data range and will be direct references to a range of cells, starting one row below the chart data range (because the first row is holding the title)

When you insert a new row after the title row, the chart data range formula now consists of two separate ranges. The newly inserted row is not included. The series definition has shifted down.

So, you have two options:

Either re-apply the data range with a macro, or use dynamic ranges for each of the series definitions.

Since you apparently have code that inserts the new row, you can simply add this line to the code to re-define the chart source (just change the "Chart 1" to the actual name of your chart.

    ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=Range("CompletionByTheArea")

Open in new window


cheers, teylyn
0
 

Author Closing Comment

by:RWayneH
ID: 36590973
I am still not getting the manual way of setting this up, but the VBA worked great!!  I wonder why the named chart range does not expand?  as it does on the sheet..  one would think that the chart range would expand too?  The VBA work for m needs.  THANKS!!  -R-
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now