automtically change ranges in formulas when new data is entered

[JRoyer95]

Using Excel 2003. New data is added to an exsisting worksheet on a daily
basis. I want my formulas to always apply to the last five cells of data
entered without having to manually update the ranges. For example: Today,
my range is A1:A5. Tomorrow, the range for the formula will be A2:A6. Is
there a way to automatically update the ranges after a new cell is entered?
Thank you!

[Ron Coderre]

Try this:
For values in Cell A1 through whatever

B1: =SUM(OFFSET(A1,COUNTA($A:$A)-5,0,5))
Returns the sum of the last 5 items in the list.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JRoyer95" wrote:

> Using Excel 2003. New data is added to an exsisting worksheet on a daily
> basis. I want my formulas to always apply to the last five cells of data
> entered without having to manually update the ranges. For example: Today,
> my range is A1:A5. Tomorrow, the range for the formula will be A2:A6. Is
> there a way to automatically update the ranges after a new cell is entered?
> Thank you!

[Aladin Akyurek]

Moreover, since you are on Excel 2003, convert the data area into a list
by means of Data|List|Create List to enjoy fully automatic adjustment of
formulas for references.

Ron Coderre wrote:
> Try this:
> For values in Cell A1 through whatever
>
> B1: =SUM(OFFSET(A1,COUNTA($A:$A)-5,0,5))
> Returns the sum of the last 5 items in the list.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "JRoyer95" wrote:
>
>
>>Using Excel 2003. New data is added to an exsisting worksheet on a daily
>>basis. I want my formulas to always apply to the last five cells of data
>>entered without having to manually update the ranges. For example: Today,
>>my range is A1:A5. Tomorrow, the range for the formula will be A2:A6. Is
>>there a way to automatically update the ranges after a new cell is entered?
>>Thank you!

[JRoyer95]

having trouble applying this formula to a row instead of a column

"Ron Coderre" wrote:

> Try this:
> For values in Cell A1 through whatever
>
> B1: =SUM(OFFSET(A1,COUNTA($A:$A)-5,0,5))
> Returns the sum of the last 5 items in the list.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "JRoyer95" wrote:
>
> > Using Excel 2003. New data is added to an exsisting worksheet on a daily
> > basis. I want my formulas to always apply to the last five cells of data
> > entered without having to manually update the ranges. For example: Today,
> > my range is A1:A5. Tomorrow, the range for the formula will be A2:A6. Is
> > there a way to automatically update the ranges after a new cell is entered?
> > Thank you!

[Ron Coderre]

If the numbers are in row 1, beginning in A1 and extending to the right...

Try this:
B1: =SUM(OFFSET(A1,0,COUNTA($1:$1)-5,1,5))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JRoyer95" wrote:

> having trouble applying this formula to a row instead of a column
>
> "Ron Coderre" wrote:
>
> > Try this:
> > For values in Cell A1 through whatever
> >
> > B1: =SUM(OFFSET(A1,COUNTA($A:$A)-5,0,5))
> > Returns the sum of the last 5 items in the list.
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "JRoyer95" wrote:
> >
> > > Using Excel 2003. New data is added to an exsisting worksheet on a daily
> > > basis. I want my formulas to always apply to the last five cells of data
> > > entered without having to manually update the ranges. For example: Today,
> > > my range is A1:A5. Tomorrow, the range for the formula will be A2:A6. Is
> > > there a way to automatically update the ranges after a new cell is entered?
> > > Thank you!

[JRoyer95]

Thanks Ron! I had tried (A1, 0, COUNTA (1:1)-5, 0, 5) and (A1, 1, COUNTA
(1:1)-5, 1, 5). This was the first time I ever had to use this formula and I
appreciate your help!

"Ron Coderre" wrote:

> If the numbers are in row 1, beginning in A1 and extending to the right...
>
> Try this:
> B1: =SUM(OFFSET(A1,0,COUNTA($1:$1)-5,1,5))
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "JRoyer95" wrote:
>
> > having trouble applying this formula to a row instead of a column
> >
> > "Ron Coderre" wrote:
> >
> > > Try this:
> > > For values in Cell A1 through whatever
> > >
> > > B1: =SUM(OFFSET(A1,COUNTA($A:$A)-5,0,5))
> > > Returns the sum of the last 5 items in the list.
> > >
> > > Does that help?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "JRoyer95" wrote:
> > >
> > > > Using Excel 2003. New data is added to an exsisting worksheet on a daily
> > > > basis. I want my formulas to always apply to the last five cells of data
> > > > entered without having to manually update the ranges. For example: Today,
> > > > my range is A1:A5. Tomorrow, the range for the formula will be A2:A6. Is
> > > > there a way to automatically update the ranges after a new cell is entered?
> > > > Thank you!