Pretty easy chart question (hopefully)

gbarclay2001 · Apr 25th, 2002, 05:14 AM

I have a chart that reports on all products that expire over the next 12 months. Several years worth of data is kept in one large table and the chart pulls the data from this.

This month it shows from April 2002- April 2003, which is fine. The problem is next month the chart's source must move to the left by a cell to pick up March 2002 - March 2003.

Is there a way to automatically do this without having to right click on the chart and manually chamge the source data.

Thanks for any ideas,

Greg

gbarclay2001 · Apr 25th, 2002, 05:15 AM

May 2002 - May 2003 even

Iain Lewis · Apr 25th, 2002, 05:37 AM

Don't know how feasible this is given that it may be a large data source, but why not have a duplicate of the source data in another sheet ( linked to the original data so that it updates as & when ). Set the chart range for the whole the duplicate data eg Jan 2002 to Dec 2003 and then just hide the columns / rows which aren't required. Then hide the sheet containing the duplicate data.

andy-s · Apr 25th, 2002, 05:37 AM

Hi,

what you probably need is a dynamic chart that always shows the last 12 values. You can see how this can be done at

http://www.erlandsendata.no/english/...ads/charts.htm

Look for "Dynamic Chart Example".

andy-s

Tom Schreiner · Apr 25th, 2002, 05:41 AM

If the headings in your source table are dates, you could place a simple set of instructions in an event code block to set the source range dynamically.
Is this the case in your case?
Tom

gbarclay2001 · Apr 25th, 2002, 05:49 AM

Hi TsTom,

The headings are months of the year.

I could probably pull the data from a pivot table to, so If I can set the criteria of a pivot table depending on the month the table would stay the same size. Is that possible?

Tom Schreiner · Apr 25th, 2002, 05:59 AM

Greg,
I don't know the first thing about pivot tables.
I am a VB programmer who is learning Excel.
There is very little you cannot do with Excel via VB or VBA but there are probably easier solutions with lookups and other available functions.
I do not know the exact setup of your table, but I think I understand what you are after.
You need a square chart source Range to move over one cell/column in your table from 04/01 to 04/02 then to 05/01 to 05/02?
You may want to hang out for a better solution. If not give me the references of your table.
Tom

gbarclay2001 · Apr 25th, 2002, 06:19 AM

Tom,

Yeah that's right. My table of source data runs from B3-Y7. This month the chart pulls the range E3-Q7 (April 02-April 03). So next month I need the source data of the graphs to change automatically to F3-R7.

Example of chart data.....

E3: April 02
E4: 4334
E5: 535
E6: 243
E7: 6785

So twelve of the above are in the graph (not quite sure what use my excellent 'example of chart data' is actually but you can have it anyway)

Greg

Tom Schreiner · Apr 25th, 2002, 06:25 AM

Ok
What is the criteria for automating the new source range?
Today's date?
For example:
If today were May 01
automatically select the new source?
Does your table just keep moving to the right every month? Indefinitely?
Also, what is the name of the worksheet holding the source range?
Will also need your chart name.

Thanks,
Tom

[ This Message was edited by: TsTom on 2002-04-25 05:32 ]

gbarclay2001 · Apr 25th, 2002, 06:36 AM

Exactly as you say. As soon as May comes along it needs to shift to the right. And yes it will move right indefinitely. The graph and source are both on the same sheet – Pipeline.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 05:14 AM	#1
gbarclay2001 New Member Join Date: Mar 2002 Location: London Posts: 46	I have a chart that reports on all products that expire over the next 12 months. Several years worth of data is kept in one large table and the chart pulls the data from this. This month it shows from April 2002- April 2003, which is fine. The problem is next month the chart's source must move to the left by a cell to pick up March 2002 - March 2003. Is there a way to automatically do this without having to right click on the chart and manually chamge the source data. Thanks for any ideas, Greg

Apr 25th, 2002, 05:15 AM	#2
gbarclay2001 New Member Join Date: Mar 2002 Location: London Posts: 46	May 2002 - May 2003 even

Apr 25th, 2002, 05:37 AM	#3
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	Don't know how feasible this is given that it may be a large data source, but why not have a duplicate of the source data in another sheet ( linked to the original data so that it updates as & when ). Set the chart range for the whole the duplicate data eg Jan 2002 to Dec 2003 and then just hide the columns / rows which aren't required. Then hide the sheet containing the duplicate data.

Apr 25th, 2002, 05:37 AM	#4
andy-s Board Regular Join Date: Mar 2002 Posts: 51	Hi, what you probably need is a dynamic chart that always shows the last 12 values. You can see how this can be done at http://www.erlandsendata.no/english/...ads/charts.htm Look for "Dynamic Chart Example". andy-s

Apr 25th, 2002, 05:41 AM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	If the headings in your source table are dates, you could place a simple set of instructions in an event code block to set the source range dynamically. Is this the case in your case? Tom

Apr 25th, 2002, 05:49 AM	#6
gbarclay2001 New Member Join Date: Mar 2002 Location: London Posts: 46	Hi TsTom, The headings are months of the year. I could probably pull the data from a pivot table to, so If I can set the criteria of a pivot table depending on the month the table would stay the same size. Is that possible?

Apr 25th, 2002, 05:59 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Greg, I don't know the first thing about pivot tables. I am a VB programmer who is learning Excel. There is very little you cannot do with Excel via VB or VBA but there are probably easier solutions with lookups and other available functions. I do not know the exact setup of your table, but I think I understand what you are after. You need a square chart source Range to move over one cell/column in your table from 04/01 to 04/02 then to 05/01 to 05/02? You may want to hang out for a better solution. If not give me the references of your table. Tom

Apr 25th, 2002, 06:19 AM	#8
gbarclay2001 New Member Join Date: Mar 2002 Location: London Posts: 46	Tom, Yeah that's right. My table of source data runs from B3-Y7. This month the chart pulls the range E3-Q7 (April 02-April 03). So next month I need the source data of the graphs to change automatically to F3-R7. Example of chart data..... E3: April 02 E4: 4334 E5: 535 E6: 243 E7: 6785 So twelve of the above are in the graph (not quite sure what use my excellent 'example of chart data' is actually but you can have it anyway) Greg

Apr 25th, 2002, 06:25 AM	#9
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Ok What is the criteria for automating the new source range? Today's date? For example: If today were May 01 automatically select the new source? Does your table just keep moving to the right every month? Indefinitely? Also, what is the name of the worksheet holding the source range? Will also need your chart name. Thanks, Tom [ This Message was edited by: TsTom on 2002-04-25 05:32 ]

Apr 25th, 2002, 06:36 AM	#10
gbarclay2001 New Member Join Date: Mar 2002 Location: London Posts: 46	Exactly as you say. As soon as May comes along it needs to shift to the right. And yes it will move right indefinitely. The graph and source are both on the same sheet – Pipeline.