Thread: Data selection on line chart

I'm collecting data but only want to chart the last 60 points on my line chart.
To do this I have to manually update the series value information in the source data.
for example: I have 176 rows of data and the chart series
value is "=sheet1$b$117:$b$176" if I add a new row I have to
change this to "=sheet1$b$118:$b$177".
Is there any way to use a variable for this? say LRD is the last row of data
could I change this value statement to be "=sheet1$b$(LRD-59):$b$(LRD)
so that anytime I add a new row it would update the chart?

thanks,

Q

Hi,

You need to use dynamic named ranges. See Jon's explanation which you
can modify to plot the last 60 instead of last 12 periods.
http://peltiertech.com/Excel/Charts/DynamicLast12.html

Cheers
Andy

Qiset wrote:
> I'm collecting data but only want to chart the last 60 points on my line
> chart.
> To do this I have to manually update the series value information in
> the source data.
> for example: I have 176 rows of data and the chart series
> value is "=sheet1$b$117:$b$176" if I add a new row I have to
> change this to "=sheet1$b$118:$b$177".
> Is there any way to use a variable for this? say LRD is the last row
> of data
> could I change this value statement to be "=sheet1$b$(LRD-59):$b$(LRD)
> so that anytime I add a new row it would update the chart?
>
> thanks,
>
> Q
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Perfect! Many thanks!

Q