Formula to calculate sum based on drop down form fields in W

KLane · Guest

I am using Word 2003 and am creating a performance review form. I have 13
drop down fields on all of which the choices can only be 1, 2, 3, 4 or 5. On
the final blank I would like to write a formula which calculates the total of
all the rankings listed.

I have tried EVERYTHING! =SUM(above), =SUM(b2:b14),
=SUM(dropdown1:dropdown13). The colon returns a syntax error. I have listed
them all out with comma separators with no colon and that returns a 0. And,
of course, I have done with and without Ctrl-F9 brackets. I am now begging
for any help you can give. Thank you.

Google · Sponsor

Greg Maxey · Guest

You might be able to do it with a formula field, but I don't know how. I
would use a macro set to run on exit from each variable dropdown field.
Something like this:

Sub TallyResults()

Dim FF As FormField, DDFresult As Long
DDFresult = 0
For Each FF In ActiveDocument.FormFields
If FF.Type = wdFieldFormDropDown Then
If FF.Result = "Superior" Then
DDFresult = DDFresult + 5
ElseIf FF.Result = "Above Average" Then
DDFresult = DDFresult + 4
ElseIf FF.Result = "Average" Then
DDFresult = DDFresult + 3
ElseIf FF.Result = "Below Average" Then
DDFresult = DDFresult + 2
Else
DDFresult = DDFresult + 1
End If
End If
Next FF
ActiveDocument.FormFields("Result").Result = DDFresult

End Sub

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

"KLane" <KLane@discussions.microsoft.com> wrote in message
news:C6A0210F-E478-478B-9B30-837823586012@microsoft.com...

Doug Robbins - Word MVP · Guest

Greg,

This is a bit simpler

Dim FF as FormField
Dim Score as Long
Score = 0
With ActiveDocument
For each FF in .FormFields
If FF.Type = wdFieldFormDropDown then Score = Score +
FF.DropDown.Value
Next FF
.Formfields("Result").Result=Score
End With

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Greg Maxey" <gmaxey@mvps.oSCARrOMEOgOLF> wrote in message
news:O$9W9SrNIHA.748@TK2MSFTNGP04.phx.gbl...

macropod · Guest

Hi KLane,

A formula field coded as:
{={REF DropDown1}+{REF DropDown2}+{REF DropDown3}+{REF DropDown4}+{REF DropDown5}}
will sum the values from your 5 Dropdown fields.

Note: use Ctrl-F9 to create the field braces (ie '{}').

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"KLane" <KLane@discussions.microsoft.com> wrote in message news:C6A0210F-E478-478B-9B30-837823586012@microsoft.com...

Greg Maxey · Guest

Doug,

I agree. I only posted an example that I had lying about that had dropdown
values of "Superior," "Excellent," "Good," etc.

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Doug Robbins - Word MVP wrote:

KLane · Guest

That worked perfectly! Thank you very much.

"macropod" wrote: