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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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.
 
Upvote 0
Yes give it a go, change the activesheet part to sheets("Sheet1) and change the chart name.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,084
Messages
6,128,728
Members
449,465
Latest member
TAKLAM

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
Back
Top