Updating Charts

Hi all,

Just wondering if someone can point me to a thread or give me advice on how to have a chart update its values after the data ranges have been updated? Specifically, I have a sheet that gets updated monthly, adding the new months data for that update. How can I apply this kind of logic to charts? Here's the recorded macro I have:

    Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets("Graphs").Range("L4"), PlotBy:= _
        xlRows
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=Monthly!R5C6:R5C65"
    ActiveChart.SeriesCollection(1).Values = "=Monthly!R7C6:R7C62"
    ActiveChart.SeriesCollection(1).Name = "=Monthly!R7C5"
    ActiveChart.SeriesCollection(2).Values = "=Monthly!R8C6:R8C62"
    ActiveChart.SeriesCollection(2).Name = "=Monthly!R8C5"
    ActiveChart.SeriesCollection(3).Values = "=Monthly!R9C6:R9C62"
    ActiveChart.SeriesCollection(3).Name = "=Monthly!R9C5"
    ActiveChart.SeriesCollection(4).Values = "=Monthly!R10C6:R10C62"
    ActiveChart.SeriesCollection(4).Name = "=Monthly!R10C5"
    ActiveChart.SeriesCollection(5).Values = "=Monthly!R11C6:R11C62"
    ActiveChart.SeriesCollection(5).Name = "=Monthly!R11C5"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Chart 1 Inustry Price Index"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    ActiveChart.PlotArea.Select

Thankee big-big!

You can use named ranges in order to have dynamic charts.
http://peltiertech.com/Excel/Charts/Dynamics.html

Thanks Andy, I've been exploring other threads and especially, looking at your website. I think I'll be able to come up with something today (if IT can fix my computer).

Hi Andy, I've been trying to implement your scroller.xls into one of our workbooks but am having troubles. Particularly with regards to the "SCROLL_LABELS" and "SCROLL_DATA". In your file, you have this commented out:

'OFFSET(Sheet1!$B$1,Sheet1!$E$2,0,Sheet1!$E$3,1)

You state the data set in your example references these named ranges and I get that. What I do not get is where this formula is in the workbook? When I look at the screen shot you provide, the "Values:" has:

scroller.xls!SCROLL_DATA

but I do not see where you set the formula for "SCROLL_DATA". I am confusing my issue here I think but I have been trying to figure this out without much luck. Can you please point me in the right direction on this?

Kindest Regards:

in xl2003 use Insert > Names > Define
in xl2007 use Formula > Name Manager

You will find the names there.

Hi Andy, thanks so far for your help. I'm not quite understanding the formula now. I now that you have yours set up to scroll down the rows but what about if I need to go across the columns? I've attached a small workbook with one field that I am trying to emulate from your example but there is no data showing up. I don't mean to be a pain in your behind but I have trouble understanding spreadsheet formulas.

Ok, so I changed the formula a bit so that it looks like this:

SCROLL_DATA '=OFFSET(Sheet1!$C$6:$N$6,0,Sheet1!$B$1)
SCROLL_LABEL '=OFFSET(Sheet1!$C$5:$N$5,0,Sheet1!$B$1,1)

Now the problem is this: I can get the axis to display months (Jan Feb ...) or Years ( 2006 2007...) but not both. The user would want months and years. Tips?

Because you have the year in merged cells it would be best to create the month/year date in the cells above the current data table.
So enter the dates in row 3.

Then alter your named ranges.

SCROLL_LABEL: =OFFSET(Sheet1!$C$3,0,Sheet1!$B$1,1,Sheet1!$C$1)
SCROLL_DATA: =OFFSET(SCROLL_LABEL,3,0)

Thank you very much Andy. We have so much historical data that has not been graphed but now it will be, which will make my department a happy department.