Mick
Administrator
1496 Posts 
Posted  09/28/2006 : 20:53:49

During my last Excel presentation, someone asked about having a table of data, plus calculation formulas, such that when you add new data, the formula is automatically copied down to complete the data. For example, a check register which automatically calculates the balance when the data for a new check is entered. I didn't know the answer, but I filed it away, and I now have the answer. It is in the "ToolsOptionsEdit" settings.
If you turn on "Extend Data range formats and formulas" Excel will automatically fill in a formula just like the one above, if it thinks the new line of data is sufficiently similar to the rows above.
The "help" file says this: Extend list formats and formulas Select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.
Of course, I have no clue who asked the question...
Mick
 'Everything has been said before, but since nobody listens we have to keep going back and beginning all over again.' —André Gide
or
'No one really listens to anyone else, and if you try it for a while you'll see why.'  Mignon McLaughlin

