Variable Range to be used in a chart
 

I would like to set up a variable range that I can use in a chart.

I am tracking five columns of data that I add to every day. I would like to
track the most recent three weeks in a chart.

I am thinking that this should be do-able using some variation of the offset
function and the Max function but I am not really sure how to set it up any
ideas would be appreciated

=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A$2:$A$550) ,1)





--
Jeff

Variable Range to be used in a chart
 

Variable ranges are also known as dynamic named ranges
and are very well explained by Debra Dalgleish at
http://www.contextures.com
Here is a charting example.
Assume this represents data for the last 14 days
and you want to XY plot the last 5 days:

XData YData
2.9 19.4
3.5 18.1
3.6 17.7
4.7 16.5
6.9 16.7
7.5 15.6
8.2 14.3
9.1 12.1
9.5 12.4
10.6 10.3
12.6 9.3
13.4 8.3
13.3 8.0
15.2 6.8

Insert Name Define Names in workbook XData
Refers To: =$A$2:$A$1000
(make it much longer than the actual data)
Also define these names
YData
Refers To: =$B$2:$B$1000
DLen
Refers To: =COUNTA(XData)
DSub
Refers To: =5
XPlot
Refers To: =INDEX(XData,DLen-DSub+1):INDEX(XData,DLen)
YPlot
Refers To: =INDEX(YData,DLen-DSub+1):INDEX(YData,DLen)
Chart Source Data X Values: =Sheet1!XPlot
Y Values: =Sheet1!YPlot
As you add data to XData and YData, the chart will
automatically update.