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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Georgiboy

Board Regular
Joined
Nov 7, 2008
Messages
114
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
114
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,516
Messages
5,596,617
Members
414,081
Latest member
Subaru_Steve

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