Thanks to all who replied.
I eventually achieved what I wanted by plotting the chart against named
ranges, and using VBA to redefine the ranges to which those named applied.
The charts automtically updated based on the redefined ranges.
I shouldn't have been so lazy!

Regards & have a good weekend.

Pete



"Peter Rooney" wrote:

Good morning all.

I haven't really done a huge amount on charting, so I'm sure you'll think
that these questions are a bit simplistic, but here goes!

I plot a chart from a worksheet that contains three columns of data - the X
axis contains week numbers, and is in B29:B54. Actual hours are entered in
F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the
form: =SUM($F$29:F29)

So, each week, in order to plot another week's data, I have to copy this
cumulative formula down by one cell, which, considering I have a lot of these
reports, is a bit time consuming.

If I replace my cumulative formula with something that displays a zero or a
label if there is no corresponding weekly actual, the cumulative chart line
plummets down to zero, which makes the charts look unprofessional. If I
replace it with something that picks up the previous week's cumulative value
if there is no corresponding weekly actual, the cumulative chart line goes
across at a value equal to the latest cumulative, which also doesn't look too
good.

So, the question is, how, in this week for example, can I have my chart area
as b29:B54 (x-axis);f29:f54 (actuals); g29:f40 (cumulatives), but next week
extend
my cumulative plot to g29:f41 (i.e. extend it down by one week)?

Eventiually, I'd like to etend this idea to plotting a rolling 12 week
chart, so to start, it would plot b29:B38 (x-axis);f29:f38 (actuals); g29:f38
(cumulatives) then the next week, b30:B39 (x-axis);f30:f39 (actuals); g30:f39
(cumulatives) and so on, but one thing at a time.

If anyone has any good pointers as to how I might achieve some or all of the
above, I'd be extremely grateful.

Regards & thanks in advance.

Pete