Word table formulas

[S Bernhard]

I am working in Word 97 SR-2 on a Windows NT network.
We have a document available to multiple users with a two-column table set up for numerical entries. We've been using it as a paper form submission which was hand-totaled. The powers that be have requested that those columns be set up so they can be totaled automatically as each user makes their entries and submitted electronically. I have tried entering zeros in all cells and using the =sum(above) and autosum commands as well as setting up a sum formula using cell addresses. I have also tried setting it up in Excel and importing it into Word, but once it is saved in Word it doesn't seem to hold the ability to tabulate as the numbers are entered. I am a newby to macros, so don't know what or how to write one for this application. Any suggestions?
Thanks,

[Andrew Cronnolly]

When you entered the Sum(above) formula did you just type it in or did you use the Table|Formula menu ? You can enter it manually provide you press Ctrl-F9 first and then between the two resultant Braces, {}, enter =Sum(Above). The formula should look like <big>{=Sum(Above)}</big>

When you have done that right click on the formula and select Update Field, right click again and select Toggle Field Codes.

If that does not sort the problem let us know the specific error you get with the Sum(above) formula.

Regards,

Andrew C

[Andrew Lockton]

You need to update the field in order to see the new number. You can put in a macro if you need to but just clicking on the field and pressing F9 would do the same.

[Gary Frieder]

This can also be done by setting the document up as a protected form, and having On Exit macros which do the calculations automatically every time an entry is made.

It's a bit of work to set up, but it's nice and secure in that the users can't readily tamper with the totals.

(and a good use for a Word form, Chris!)

[gwhitfield]

Hi,

Actually, the "Sum(ABOVE)" formula does not calculate in all cases- see <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q185/0/56.ASP>the Microsoft Knowledgebase article</A> on this.

I prefer the second workaround mentioned- using Excel-type references: "{Sum(A1:A5)}"

[gwhitfield]

The coding on the on exit macro would look something like this:

Sub RefreshFields()
ActiveDocument.Fields.Update
End Sub


After adding the macro, you can then set the "On Exit" macro to "Refresh Fields" for any (and every) form field which is included in the calculation, as Gary says.

[Gary Frieder]

Yeesh. Shows you how backwards I am about fields:
I just finished creating an overtime form. Didn't use calculation fields - instead I built functions for all the different calculations required - these are called from the OnExit macros - three pages of lovely code. Great exercise, I guess....[img]/w3timages/icons/doh.gif[/img] - at least they work reliably, unlike Sum[img]/w3timages/icons/grin.gif[/img]

[S Bernhard]

Thanks for the suggestions. I had the formula correct, but did not update the field after each entry. It works perfectly.

Sandy