ICON Home Page ICON Members' Forums Home Page
Members' Forums
Username:
Password:
Save Password Forgot your Password?

Home | Profile | Register | Active Topics | Members | Search | FAQ
 
 All ICON Forums
 Help Forums
 Office Applications
 Auto-add formulas in Excel tables
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mick
Administrator

1496 Posts

Posted - 09/28/2006 :  20:53:49  Show Profile  Visit Mick's Homepage
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 "Tools-Options-Edit" 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
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
ICON Computer Users Group © Interactive Computer Owners' Network Go To Top Of Page
Snitz Forums 2000