automtically change ranges in formulas when new data is entered
 

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!

automtically change ranges in formulas when new data is entered
 

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!

automtically change ranges in formulas when new data is entered
 

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!

automtically change ranges in formulas when new data is entere
 

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!

automtically change ranges in formulas when new data is entere
 

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!

automtically change ranges in formulas when new data is entere
 

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!