Automatically shift chart data range: redux

[Sean Clayton]

I posted this a couple of months ago, and the thread was dropped after
a couple of replies. Now I find myself needing to try again, so I'd
love if someone could offer advice.

I have a spreadsheet full of charts based on monthly data. The charts
are intended to show a year's worth of information, i.e., April 2009
to March 2010. There are a series of cells in the spreadsheet with the
chart that are autoupdated with new information that the charts are
based on.

Since there are so many of these charts to update each month, I'd like
to know if there is a way to have them automatically shift their date
ranges for each new month. For example, Chart A shows data from April
2009 to March 2010. Now that April is here, the chart needs to be
shifted to show data from May 2009 to April 2010. Is there a way to
accomplish this without having to manually update each chart?

Hugo Jorgenson said:

Hi Sean,

There is a way to do it without PivotTables. But in order to help you I need
to know one thing.

With OFFSET function and Name Ranges you can solve it. I can show you but
first you need to tell me about the data range you have today. Do you want
the chart to start from a new column or from a new row each month?

Hugo Jorgensen

In answer, the chart will start _and_ end from a new column each
month; i.e., this month will begin with Aug 2009 and go to July 2010,
while next month will begin with Sept 2009 and go to Aug 2010.

I've tried to figure this out on my own, but I haven't had much
success. Could someone offer direction?

 

[Sean Clayton]

Oh, and while I know pivot tables would solve the issue and would love
to use them, the mentality is 'don't mess with what works' so
converting the charts to pivotcharts is unfortunately out.

 

[Pete_UK]

It's difficult to offer advice when you don't describe your data
layout very well.

If you like you could send me a copy of your file (zipped if it is
large) to:

pashurst at auditel.net (change the obvious),

and I'll take a look at it. Put some comments in the file so I know
what you would like to accomplish.

Hope this helps.

Pete

 

[Sean Clayton]

Sorry about that. This thread is a repeat of one I posted back in
April, and I intended to put a link to the original in it, but it
appears I forgot.

Here's the situation. Every month, a large packet of Excel charts are
produced, showing productivity information trends over the last 12
months. The charts are based off of columns of numbers. Here is an
example:

Aug-09 Sep-09 Oct-09 Nov-09 Dec-09 Jan-10 Feb-10 Mar-10 Apr-10 May-10
Jun-10 Jul-10 Aug-10
31,218 38,546 38,227 42,981 46,751 39,946 36,874 48,254 49,183 47,226
45,964 45,964 31,218
30,776 32,652 32,757 35,206 42,769 30,210 33,903 35,541 37,506 30,814
24,232 24,232 30,776
3,506 3,922 3,686 3,725 4,195 3,742 3,602 4,053 4,084 3841
3886 3886 3,506
11.23% 10.17% 9.64% 8.67% 8.97% 9.37% 9.77% 8.40% 8.30% 8.13% 8.45%
8.45% 11.23%


The datasets for each chart must be manually moved every month to
encompass the new month. The process is simple, although very time
consuming, and the sheer number of charts to change introduces the
chance for error.

I can't send you the original file; it contains what might be
construed as sensitive information, but I've made an accurate example
and will send it along. In short, all I'm trying to do is find an
automatic or as little manual intervention as possible way to move the
dataset for each chart forward one month, dropping off one month -
using the above example, moving forward to include Jul-10 while
dropping off Aug-09.

 

[Pete_UK]

I look forward to receiving your anonomised file. My first instinct is
to drive the charts not from this data directly, but from another set
of data which is derived from this - then it is quite easy to select a
new start month and your chart(s) will not need to be changed at all.

Pete

 

[Pete_UK]

I've returned an example file back to you.

Hope this helps.

Pete

 

[Jon Peltier]

Sean -

The Change Series Formula utility at the end of this article might be
just what you need.

How to Edit Series Formulas
http://peltiertech.com/WordPress/how-to-edit-series-formulas/

- Jon