Dynamic Charts

[Guest]

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

 

[Guest]

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

 

[Spreadsheet Solutions]

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

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Many thanks for the link, it works!

Cheers
Nigel

 

[Guest]

Many thanks for the link. that is the answer.

Cheers

 

[Ken]

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

 

[Guest]

Thank you - another example that helps.

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

 

[Guest]

Many thanks a useful reference

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

 

[Guest]

Thanks Ken , works great.

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

 

[Jon Peltier]

I have an index too <g>:

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

- Jon