Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

Yes, perfectly possible; take a look at
http://www.tushar-mehta.com/excel/ne...rts/index.html
whe
2)Graph only the last so many entries in a data series
might be of use.
--
p45cal


"Nigel RS" wrote:

Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

Check Jon Peltiers website.
He's the man when talking about chrts....

http://peltiertech.com/Excel/Charts/DynamicLast12.html


--
Mark Rosenkrantz
--
Spreadsheet Solutions
Witkopeend 24
1423 SN, Uithoorn
Netherlands
--
W: www.rosenkrantz.nl
E:
--
"Nigel RS" wrote in message
...
Hi All,
I have a set of embedded charts (linked to worksheet data). Typically
these
might show the last 12 months of data, but it may be other fixed periods
as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am
displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and
error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

this page at Debra Dalgleish's site shows how to construct a dynamic range

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

You would modify it using calculated arguements of the OFFSET or INDEX
function. These would be used in a defineded name which would be used as the
source for your chart.

--
Regards,
Tom Ogilvy


"Nigel RS" wrote:

Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

Also at Debra's site is an index into Jon Peltier's site. Look in that index
for dynamic chart:

http://www.contextures.com/JPChartIndex.htm

--
Regards,
Tom Ogilvy


"Nigel RS" wrote:

Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

Many thanks for the link, it works!

Cheers
Nigel

"p45cal" wrote:

Yes, perfectly possible; take a look at
http://www.tushar-mehta.com/excel/ne...rts/index.html
whe
2)Graph only the last so many entries in a data series
might be of use.
--
p45cal


"Nigel RS" wrote:

Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

Many thanks for the link. that is the answer.

Cheers


"p45cal" wrote:

Yes, perfectly possible; take a look at
http://www.tushar-mehta.com/excel/ne...rts/index.html
whe
2)Graph only the last so many entries in a data series
might be of use.
--
p45cal


"Nigel RS" wrote:

Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

The OFFSET function is very useful in this effort. If you give the
data to be charted a defined name, and define the name using the
offset function you can do exactly what you want. For example (copied
from a dynamic chart of mine), if you have a range named "Actual",
defined as

=OFFSET(combined!$C$3,COUNTA(combined!$C:$C)-3,1)

where your data is in column C and you assign "Actual" to be the
values for your series, then your series will grow as the range
increases. In this case, I have 3 non-blank cells in column C that
are not part of the data, the the data starts in row 4; hence, I
offset from row 3 by the total number of non-blank cells in column C,
less 3. When assigning the named range to the values, you need to
include the sheet name.

Good luck.


Ken
Norfolk, Va




On Jun 25, 8:15 am, Nigel RS
wrote:
Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

Thank you - another example that helps.

"Spreadsheet Solutions" wrote:

Check Jon Peltiers website.
He's the man when talking about chrts....

http://peltiertech.com/Excel/Charts/DynamicLast12.html


--
Mark Rosenkrantz
--
Spreadsheet Solutions
Witkopeend 24
1423 SN, Uithoorn
Netherlands
--
W: www.rosenkrantz.nl
E:
--
"Nigel RS" wrote in message
...
Hi All,
I have a set of embedded charts (linked to worksheet data). Typically
these
might show the last 12 months of data, but it may be other fixed periods
as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am
displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and
error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

Many thanks a useful reference

"Tom Ogilvy" wrote:

Also at Debra's site is an index into Jon Peltier's site. Look in that index
for dynamic chart:

http://www.contextures.com/JPChartIndex.htm

--
Regards,
Tom Ogilvy


"Nigel RS" wrote:

Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

Thanks Ken , works great.


"Ken" wrote:

The OFFSET function is very useful in this effort. If you give the
data to be charted a defined name, and define the name using the
offset function you can do exactly what you want. For example (copied
from a dynamic chart of mine), if you have a range named "Actual",
defined as

=OFFSET(combined!$C$3,COUNTA(combined!$C:$C)-3,1)

where your data is in column C and you assign "Actual" to be the
values for your series, then your series will grow as the range
increases. In this case, I have 3 non-blank cells in column C that
are not part of the data, the the data starts in row 4; hence, I
offset from row 3 by the total number of non-blank cells in column C,
less 3. When assigning the named range to the values, you need to
include the sheet name.

Good luck.


Ken
Norfolk, Va




On Jun 25, 8:15 am, Nigel RS
wrote:
Hi All,
I have a set of embedded charts (linked to worksheet data). Typically these
might show the last 12 months of data, but it may be other fixed periods as
required.

How can I arrange for the chart ranges to dynamically change as new values
are added to next empty cell in the range? For example if I am displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks

I have an index too <g:

http://peltiertech.com/Excel/Charts/ChartIndex.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Tom Ogilvy" wrote in message
...
Also at Debra's site is an index into Jon Peltier's site. Look in that
index
for dynamic chart:

http://www.contextures.com/JPChartIndex.htm

--
Regards,
Tom Ogilvy


"Nigel RS" wrote:

Hi All,
I have a set of embedded charts (linked to worksheet data). Typically
these
might show the last 12 months of data, but it may be other fixed periods
as
required.

How can I arrange for the chart ranges to dynamically change as new
values
are added to next empty cell in the range? For example if I am
displaying
the last 12 months say Jul06 thru Jun07, when I add a value for Jul07 the
chart changes to show Aug06 thru Jul07.

Currently we edit the chart ranges manually, which is both tedious and
error
prone.

Prefer a Excel function solution but if VBA needed we can use it.

Many thanks