Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Formulas
I have a large spreadsheet that is maintained by 3 people. This spreadsheet
is broken into several sections with subtotals for each section. One of us has a habit of adding rows at the end of a section and forgetting to update the formulas. I usually learn of this error when I am preparing reports that do not balance. My practice is to add rows before the last row; move the last data row up to the first new blank row than add the new lines in the new blank rows. There has to be a better way to add rows at the bottom and have the formulas automaticlly updated. We are using 2003€¦Does anyone know of method/solution for adding rows at the bottom of a range and having the formulas automatically updated to include the new rows? -- JPS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Formulas
You should try to place and constuct formulas so they do not require updating.
A simple example: Say we have data in column A and want to sum the data. Values go in A1 thru A99 and in A100, the formula: =SUM(A1:A100) Whenever a new row of data is added, the formula must be updated. Another approach is to put the formula: =SUM(A2:A65536) in A1 and have the data start in A2. Using this approach avoids the need for updating the formula. -- Gary''s Student - gsnu200794 "JPS" wrote: I have a large spreadsheet that is maintained by 3 people. This spreadsheet is broken into several sections with subtotals for each section. One of us has a habit of adding rows at the end of a section and forgetting to update the formulas. I usually learn of this error when I am preparing reports that do not balance. My practice is to add rows before the last row; move the last data row up to the first new blank row than add the new lines in the new blank rows. There has to be a better way to add rows at the bottom and have the formulas automaticlly updated. We are using 2003€¦Does anyone know of method/solution for adding rows at the bottom of a range and having the formulas automatically updated to include the new rows? -- JPS |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Formulas
One more for your perusal.
=SUM(A1:INDEX(A:A,ROW()-1)) Entered in any cell from A2 down. Inserted rows will get picked up. Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 03:18:02 -0700, JPS wrote: I have a large spreadsheet that is maintained by 3 people. This spreadsheet is broken into several sections with subtotals for each section. One of us has a habit of adding rows at the end of a section and forgetting to update the formulas. I usually learn of this error when I am preparing reports that do not balance. My practice is to add rows before the last row; move the last data row up to the first new blank row than add the new lines in the new blank rows. There has to be a better way to add rows at the bottom and have the formulas automaticlly updated. We are using 2003…Does anyone know of method/solution for adding rows at the bottom of a range and having the formulas automatically updated to include the new rows? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas updating | Excel Discussion (Misc queries) | |||
sum formulas not updating | Excel Worksheet Functions | |||
Updating Formulas | Excel Discussion (Misc queries) | |||
Formulas updating | Excel Worksheet Functions | |||
Formulas not updating | Excel Discussion (Misc queries) |