| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
| How to change column letters to correct ones in many formulas automatically? | Excel Worksheet Functions | |||
| Sorting Data that feeds into other formulas.... | Excel Discussion (Misc queries) | |||
| When I change my data, my formulas don't update the answers,why? | Excel Discussion (Misc queries) | |||
| Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel | |||
Linear Mode
