Dynamic Chart Updating

troy_lee

Board Regular
Joined
Feb 6, 2008
Messages
169
Column A on a datasheet is the week number. This is the chart's X axis. Column C is the value that gets plotted on the Y axis.

I want to make the chart dynamic so that every week when I add a new row, the chart's data range not only expands to include the new row but also readjusts the first row of the range to be one more than the previous week. In this manner I would always have only 52 weeks graphed on the chart.

Thanks in advance for the help.

Troy
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Georgiboy

Board Regular
Joined
Nov 7, 2008
Messages
195
Something like this maybe...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim EndRow As Integer
Dim AddR As String

EndRow = Range("A" & Rows.Count).End(xlUp).Row

If Target.Column < 4 Then
    AddR = Target.Address
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!$A$" & EndRow - 51 & ":$A$" & EndRow
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!$C$" & EndRow - 51 & ":$C$" & EndRow
End If

Range(AddR).Select

End Sub

it would need to go in the sheet code window.
if you change any cell in a column <4 it will update a chart "Chart 3" in this case.

Hope this helps
 

troy_lee

Board Regular
Joined
Feb 6, 2008
Messages
169
Thanks for the help. I forgot to mention that my data is on one sheet and my chart on another. What do you recommend I change the ActiveSheet object to to refer to the sheet with the chart. The chart's sheet name is Scorecard.

Thanks.
 

Georgiboy

Board Regular
Joined
Nov 7, 2008
Messages
195
Yes give it a go, change the activesheet part to sheets("Sheet1) and change the chart name.
 

troy_lee

Board Regular
Joined
Feb 6, 2008
Messages
169
Here is my code with added parameters from my original question. I am getting a failure "Unable to set the values of the series class." Here is the code.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim EndRow As Integer
Dim AddR As String
Dim chart As chart

EndRow = Range("A" & Rows.Count).End(xlUp).Row

If Target.Column < 8 Then
    AddR = Target.Address
    Worksheets("Scorecard").ChartObjects("YTY").Activate
    'ActiveChart.SeriesCollection(1).XValues = "=Data!$A$" & EndRow - 51 & ":$A$" & EndRow
    ActiveChart.SeriesCollection("Shipped").Values = "=Data!$C$" & EndRow - 51 & ":$C$" & EndRow
    ActiveChart.SeriesCollection("Received").Values = "=Data!$G$" & EndRow - 51 & ":$G$" & EndRow
    ActiveChart.SeriesCollection("On Hand").Values = "=Data!$I$" & EndRow - 51 & ":$I$" & EndRow
End If

Range(AddR).Select

End Sub

I also have a question about referencing the X values properly. I can't see where I can assign it a name like the other series so I assume its index number is (1).

Thanks for the help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,313
Messages
5,836,594
Members
430,441
Latest member
SurendraTantia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top