extending source data in several charts

I have a big Excel doc with numerous charts, each with numerous lines.

About once per month, I need to extend the charts for about a month. This is
a very painful process for each graph (multiplied by numerous graphs!).

Does anyone have any simple method to do a mass change, standard or VBA?
This would be extremely helpful.

I also noticed some posts using OFFSET, but this would be too confusing for
my father (for whom I am doing this) and too cumbersome. Something like a
standard function or a VBA macro that I can program would make his life so
much easier.

Thanks much!

if you don't like defining the ranges via the offset method, you might try
renaming all the series as names and creating the charts via a macro

"Mike" wrote:

> I have a big Excel doc with numerous charts, each with numerous lines.
>
> About once per month, I need to extend the charts for about a month. This is
> a very painful process for each graph (multiplied by numerous graphs!).
>
> Does anyone have any simple method to do a mass change, standard or VBA?
> This would be extremely helpful.
>
> I also noticed some posts using OFFSET, but this would be too confusing for
> my father (for whom I am doing this) and too cumbersome. Something like a
> standard function or a VBA macro that I can program would make his life so
> much easier.
>
> Thanks much!

I suggest using Dynamic Named Ranges. Debra Dalgleish discusses these here:

http://www.contextures.com/xlNames01.html

Dynamic Named Ranges use worksheet functions (particularly the Offset
function) in the range's RefersTo property to enable automatic adustment of
the range definitions - i.e. they update automatically. Once established,
they should be maintenance free.

The example below assumes the chart is XY Scatter. Also assumed is that the
sheet is named "Sheet1" and that the x values are in column A starting in A1
and the y values are adjacent in column B. Also required for the example is
that there be no other data in these columns.

Example:
1. Open the Define Name dialog: Insert > Names > Define
2. Create the x values range:
i. In the "Names in workbook:" window enter the name "XVals"
ii. In the "Refers to:" window enter the following formula:
=OFFSET(Sheet1!$A$1, 0, 0, COUNT(Sheet1!$A:$A), 1)
iii. Click the Add button
3. Now create another range for the y values:
i. In the "Names in workbook:" window enter the name "YVals"
ii. In the "Refers to:" window enter:
=Offset(XVals, 0, 1)
iii. Click the Add button
4. Click the Close button
5. Now change the chart's source data to reference these names:
i. Right-click the chart and select Source Data
ii. Select the Series tab (if not already active)
iii. Ensure that "Series1" is selected in the "Series" window
iv. In the "X Values:" window enter: =Sheet1!XVals
v. In the "Y Values:" window enter: =Sheet1!YVals

The Copy and Paste functions are not available when either the Define Names
and Source Data dialogs are active. To copy use:
<Ctrl> + C
To paste use:
<Ctrl> + V

Regards,
Greg


"Mike" wrote:

> I have a big Excel doc with numerous charts, each with numerous lines.
>
> About once per month, I need to extend the charts for about a month. This is
> a very painful process for each graph (multiplied by numerous graphs!).
>
> Does anyone have any simple method to do a mass change, standard or VBA?
> This would be extremely helpful.
>
> I also noticed some posts using OFFSET, but this would be too confusing for
> my father (for whom I am doing this) and too cumbersome. Something like a
> standard function or a VBA macro that I can program would make his life so
> much easier.
>
> Thanks much!

Mike,

Your mention of the Offset function likely was in reference to previous
posts describing Dynamic Named Ranges. However, I'm not suggesting that your
father maintain these. I suggest that you set it up and hencefourth it should
be maintenance free so long as no new charts are added.

If your data ranges are all in some sensible order, after creating the first
named range, the others should be easy. Examples of range definitions:

Name : Definition in RefersTo window

Chart 1:
XVals1: =Offset(Sheet1!$A$1, 0, 0, Count(Sheet1!$A:$A), 1)
YVals1: =Offset(XVals1, 0, 1)

Chart 2:
XVals2: =Offset(XVals1, 0, 2)
YVals2: = Offset(XVals1, 0, 3)

Chart 3:
XVals3: =Offset(XVals1, 0, 4)
YVals3: = Offset(XVals1, 0, 5)

etc...

The macro route is another possibility. But I would use this as the first
option. For the macro route, I think I'd use the WorksheetChange event to
loop through all the ranges and reset the source data for all charts. A bit
slower than the first option.

Regards,
Greg


"Mike" wrote:

> I have a big Excel doc with numerous charts, each with numerous lines.
>
> About once per month, I need to extend the charts for about a month. This is
> a very painful process for each graph (multiplied by numerous graphs!).
>
> Does anyone have any simple method to do a mass change, standard or VBA?
> This would be extremely helpful.
>
> I also noticed some posts using OFFSET, but this would be too confusing for
> my father (for whom I am doing this) and too cumbersome. Something like a
> standard function or a VBA macro that I can program would make his life so
> much easier.
>
> Thanks much!

Oops,

This would require that all the charts have the same number of data points.
This is not likely the case here. So disregard my previous post.

Greg

"Greg Wilson" wrote:

> Mike,
>
> Your mention of the Offset function likely was in reference to previous
> posts describing Dynamic Named Ranges. However, I'm not suggesting that your
> father maintain these. I suggest that you set it up and hencefourth it should
> be maintenance free so long as no new charts are added.
>
> If your data ranges are all in some sensible order, after creating the first
> named range, the others should be easy. Examples of range definitions:
>
> Name : Definition in RefersTo window
>
> Chart 1:
> XVals1: =Offset(Sheet1!$A$1, 0, 0, Count(Sheet1!$A:$A), 1)
> YVals1: =Offset(XVals1, 0, 1)
>
> Chart 2:
> XVals2: =Offset(XVals1, 0, 2)
> YVals2: = Offset(XVals1, 0, 3)
>
> Chart 3:
> XVals3: =Offset(XVals1, 0, 4)
> YVals3: = Offset(XVals1, 0, 5)
>
> etc...
>
> The macro route is another possibility. But I would use this as the first
> option. For the macro route, I think I'd use the WorksheetChange event to
> loop through all the ranges and reset the source data for all charts. A bit
> slower than the first option.
>
> Regards,
> Greg
>
>
> "Mike" wrote:
>
> > I have a big Excel doc with numerous charts, each with numerous lines.
> >
> > About once per month, I need to extend the charts for about a month. This is
> > a very painful process for each graph (multiplied by numerous graphs!).
> >
> > Does anyone have any simple method to do a mass change, standard or VBA?
> > This would be extremely helpful.
> >
> > I also noticed some posts using OFFSET, but this would be too confusing for
> > my father (for whom I am doing this) and too cumbersome. Something like a
> > standard function or a VBA macro that I can program would make his life so
> > much easier.
> >
> > Thanks much!