Getting charts to automatically pick data from a series

Can I get a chart to always use say the last 9 values of a series?

Does excel also do interactive charts?

Salmonh:

Yes you can have Excel chart the last 9, 30 or whatever number of values
that you would like.

You'll need to use dynamic range names to do this. Here are several links
that will get you started.


http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
http://processtrends.com/pg_chart_ti...rt_Range_Names
http://peltiertech.com/Excel/Charts/Dynamics.html


The idea is to create dynamic ranges that will update as you add data and
will set the last 9 values as your plot range.

For a simple data set of X and Y in Cols A and B, you would use 4 offset
formulas:

First we set up a range name for the last data row

last_row =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)

Second: we set up a range name for the first data row that we want to plot,
using your 9 example:

first_row
=OFFSET(last_row,-8,0)


Third: we set up the dynamic X value range name using the first and last
rows:

dyn_x =OFFSET(first_row,0,0,last_row-first_row+1)


Fourth: we set up the dynamic Y value range as an offset to the dynamic_X
range

dyn_y =OFFSET(dyn_x,0,1)


We are now set to create our chart. I do this by creating my chart using the
chart wizard. When I have my chart formatted and set the way I want, I edit
the chart series formulas and replace the X value range with dyn_X and the Y
value range with the dyn_Y range name.

If you want another period besides 9, edit the offset formulas.


You also asked about interactive charts. There are many interactive
examples. If you give me an idea of the type of interaction you are looking
for, I can give you links to specific examples.

...Kelly

koday@processtrends.com




"Salmonh20" <Salmonh20@discussions.microsoft.com> wrote in message
news:7C6C7AA7-19F3-4096-8BD0-EF3C974A1B87@microsoft.com...
> Can I get a chart to always use say the last 9 values of a series?
>
> Does excel also do interactive charts?
>
>

This one shows the last 12 values, which is easily adjusted to 9:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"Salmonh20" <Salmonh20@discussions.microsoft.com> wrote in message
news:7C6C7AA7-19F3-4096-8BD0-EF3C974A1B87@microsoft.com...
> Can I get a chart to always use say the last 9 values of a series?
>
> Does excel also do interactive charts?
>
>

Kelly,

Thanks that was really helful.
Re: Interactive charts, on a set dat I have 20 different lab results on a
sample, I have 500 sample per year.
I would like to send the results as an interactive chart which will allow
the user to pick from the 20 different result types, what they wish to view,
changing from one to another.

Robert

"Kelly O'Day" wrote:

> Salmonh:
>
> Yes you can have Excel chart the last 9, 30 or whatever number of values
> that you would like.
>
> You'll need to use dynamic range names to do this. Here are several links
> that will get you started.
>
>
> http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
> http://processtrends.com/pg_chart_ti...rt_Range_Names
> http://peltiertech.com/Excel/Charts/Dynamics.html
>
>
> The idea is to create dynamic ranges that will update as you add data and
> will set the last 9 values as your plot range.
>
> For a simple data set of X and Y in Cols A and B, you would use 4 offset
> formulas:
>
> First we set up a range name for the last data row
>
> last_row =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0)
>
> Second: we set up a range name for the first data row that we want to plot,
> using your 9 example:
>
> first_row
> =OFFSET(last_row,-8,0)
>
>
> Third: we set up the dynamic X value range name using the first and last
> rows:
>
> dyn_x =OFFSET(first_row,0,0,last_row-first_row+1)
>
>
> Fourth: we set up the dynamic Y value range as an offset to the dynamic_X
> range
>
> dyn_y =OFFSET(dyn_x,0,1)
>
>
> We are now set to create our chart. I do this by creating my chart using the
> chart wizard. When I have my chart formatted and set the way I want, I edit
> the chart series formulas and replace the X value range with dyn_X and the Y
> value range with the dyn_Y range name.
>
> If you want another period besides 9, edit the offset formulas.
>
>
> You also asked about interactive charts. There are many interactive
> examples. If you give me an idea of the type of interaction you are looking
> for, I can give you links to specific examples.
>
> ...Kelly
>
> koday@processtrends.com
>
>
>
>
> "Salmonh20" <Salmonh20@discussions.microsoft.com> wrote in message
> news:7C6C7AA7-19F3-4096-8BD0-EF3C974A1B87@microsoft.com...
> > Can I get a chart to always use say the last 9 values of a series?
> >
> > Does excel also do interactive charts?
> >
> >
>
>
>