Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.tables
KLane KLane is offline
external usenet poster
 
Posts: 2
Default Formula to calculate sum based on drop down form fields in Word

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.
  #2   Report Post  
Posted to microsoft.public.word.tables
Greg Maxey Greg Maxey is offline
external usenet poster
 
Posts: 285
Default Formula to calculate sum based on drop down form fields in Word

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" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.word.tables
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Formula to calculate sum based on drop down form fields in Word

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" wrote in message
...
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" wrote in message
...
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.





  #4   Report Post  
Posted to microsoft.public.word.tables
macropod macropod is offline
external usenet poster
 
Posts: 1,002
Default Formula to calculate sum based on drop down form fields in Word

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" wrote in message ...
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.

  #5   Report Post  
Posted to microsoft.public.word.tables
Greg Maxey Greg Maxey is offline
external usenet poster
 
Posts: 285
Default Formula to calculate sum based on drop down form fields in Word

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:
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


"Greg Maxey" wrote in message
...
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" wrote in message
...
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.





  #6   Report Post  
Posted to microsoft.public.word.tables
KLane KLane is offline
external usenet poster
 
Posts: 2
Default Formula to calculate sum based on drop down form fields in Wor

That worked perfectly! Thank you very much.

"macropod" wrote:

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" wrote in message ...
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.


  #7   Report Post  
Posted to microsoft.public.word.tables
pedro gracio pedro gracio is offline
external usenet poster
 
Posts: 1
Default Formula to calculate sum based on drop down form fieldsin Word - macropod

Hi,

This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need some sort of refresh.

I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would be quite painful for the user.

Here's that code :

Sub AddDropDownResults()

Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
' Get value of first drop down form field.
dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
' Get value of second drop down form field.
dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
' Get value of thrid drop down form field.
dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
' Get value of fouth drop down form field.
dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
' Get value of fifth drop down form field.
dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
' Get value of sixth drop down form field.
dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
' Get value of seveth drop down form field.
dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
' Calculate results and place in Text1 form field
ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)

End Sub


Any help would be greatly appreciated

Thanks

Pedy
  #8   Report Post  
Posted to microsoft.public.word.tables
macropod macropod is offline
external usenet poster
 
Posts: 1,002
Default Formula to calculate sum based on drop down form fields in Word - macropod

Hi pedro,

You don't need a macro to do the calculation. All you need is a formula field (not a formfield), coded as:
{=DropDown1+DropDown2+DropDown3+DropDown4+DropDown 5+DropDown6+DropDown7}
where the field braces (ie '{ }') are created via Ctrl-F9.

As for the TOC, the best way to update it in a form is to use a macro to temporarily unprotect the form, update the TOC, then
reprotect the form (with noreset = true).

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

"pedro gracio" wrote in message ...
Hi,

This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need
some sort of refresh.

I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would
be quite painful for the user.

Here's that code :

Sub AddDropDownResults()

Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
' Get value of first drop down form field.
dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
' Get value of second drop down form field.
dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
' Get value of thrid drop down form field.
dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
' Get value of fouth drop down form field.
dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
' Get value of fifth drop down form field.
dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
' Get value of sixth drop down form field.
dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
' Get value of seveth drop down form field.
dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
' Calculate results and place in Text1 form field
ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)

End Sub


Any help would be greatly appreciated

Thanks

Pedy


  #9   Report Post  
Posted to microsoft.public.word.tables
macropod macropod is offline
external usenet poster
 
Posts: 1,002
Default Formula to calculate sum based on drop down form fields in Word - macropod

Hi pedro,

Formula Correction:
{={DropDown1}+{DropDown2}+{DropDown3}+{DropDown4}+ {DropDown5}+{DropDown6}+{DropDown7}}

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

"macropod" wrote in message ...
Hi pedro,

You don't need a macro to do the calculation. All you need is a formula field (not a formfield), coded as:
{=DropDown1+DropDown2+DropDown3+DropDown4+DropDown 5+DropDown6+DropDown7}
where the field braces (ie '{ }') are created via Ctrl-F9.

As for the TOC, the best way to update it in a form is to use a macro to temporarily unprotect the form, update the TOC, then
reprotect the form (with noreset = true).

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

"pedro gracio" wrote in message ...
Hi,

This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need
some sort of refresh.

I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would
be quite painful for the user.

Here's that code :

Sub AddDropDownResults()

Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
' Get value of first drop down form field.
dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
' Get value of second drop down form field.
dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
' Get value of thrid drop down form field.
dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
' Get value of fouth drop down form field.
dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
' Get value of fifth drop down form field.
dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
' Get value of sixth drop down form field.
dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
' Get value of seveth drop down form field.
dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
' Calculate results and place in Text1 form field
ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)

End Sub


Any help would be greatly appreciated

Thanks

Pedy



  #10   Report Post  
Posted to microsoft.public.word.tables
[email protected] pedygbinuss@gmail.com is offline
external usenet poster
 
Posts: 1
Default Formula to calculate sum based on drop down form fields in Word -macropod

On Apr 17, 11:18*pm, "macropod" wrote:
Hi pedro,

Formula Correction:
{={DropDown1}+{DropDown2}+{DropDown3}+{DropDown4}+ {DropDown5}+{DropDown6}+{*DropDown7}}

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



"macropod" wrote in . ..
Hi pedro,


You don't need a macro to do the calculation. All you need is a formula field (not a formfield), coded as:
{=DropDown1+DropDown2+DropDown3+DropDown4+DropDown 5+DropDown6+DropDown7}
where the field braces (ie '{ }') are created via Ctrl-F9.


As for the TOC, the best way to update it in a form is to use a macro to temporarily unprotect the form, update the TOC, then
reprotect the form (with noreset = true).


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


"pedro gracio" wrote in ...
Hi,


This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need
some sort of refresh.


I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course would
be quite painful for the user.


Here's that code :


Sub AddDropDownResults()


* * *Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
* * *' Get value of first drop down form field.
* * *dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
* * *' Get value of second drop down form field.
* * *dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
* * *' Get value of thrid drop down form field.
* * *dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
* * *' Get value of fouth drop down form field.
* * *dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
* * *' Get value of fifth drop down form field.
* * *dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
* * *' Get value of sixth drop down form field.
* * *dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
* * *' Get value of seveth drop down form field.
* * *dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
* * *' Calculate results and place in Text1 form field
* * *ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)


End Sub


Any help would be greatly appreciated


Thanks


Pedy- Hide quoted text -


- Show quoted text -


Hi macropod,

Sorry for re-posting, I did not see your reply...

How would I create a "macro to temporarily unprotect the form" ?

The other thing that I tried and it worked great until I placed the
table in a document with a TOC is just using a regular Text Form Field
type Number with the calculate on exit, and in the last cell I used
the formula "=SUM(d2:d21)". This again woked just fine, it calculated
automatically & all but once there is a TOC it didn't do anything...

pedy


  #11   Report Post  
Posted to microsoft.public.word.tables
macropod macropod is offline
external usenet poster
 
Posts: 1,002
Default Formula to calculate sum based on drop down form fields in Word - macropod

Hi pedro,

You could use an 'on exit' macro like the following attached to a formfield:
Sub UpdateDoc()
Dim ToC As TableOfContents
Dim pRange As Word.Range
Dim Pwd As String
Dim pState As Boolean
With ActiveDocument
pState = False
If .ProtectionType wdNoProtection Then
Pwd = InputBox("PleasePassword", "Password")
pState = True
.Unprotect Pwd
End If
' Loop through Story Ranges and update.
' Note that this may trigger interactive fields (eg ASK and FILLIN).
For Each pRange In .StoryRanges
Do
pRange.Fields.Update
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
' Loop through Tables Of Contents and update
For Each ToC In .TablesOfContents
ToC.Update
Next
If pState = True Then .Protect wdAllowOnlyFormFields, Noreset:=True, Password:=Pwd
pState = False
Pwd = ""
End With
End Sub

The code asks the use to input the password. If you don't want to do that, replace:
Pwd = InputBox("PleasePassword", "Password")
with:
Pwd = "Password"
where "Password" is the actual password.

Note that the macro includes code to update the ToC also, since it's difficult to get ToCs to update correctly in a form. The same
applies to Tables of Authorities and Tables of Figures, but you haven't mentioned the document having any of them. You can delete
the code to update the ToC if none of your formfields can change the document's pagination.

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

wrote in message ...
On Apr 17, 11:18 pm, "macropod" wrote:
Hi pedro,

Formula Correction:
{={DropDown1}+{DropDown2}+{DropDown3}+{DropDown4}+ {DropDown5}+{DropDown6}+{*DropDown7}}

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



"macropod" wrote in . ..
Hi pedro,


You don't need a macro to do the calculation. All you need is a formula field (not a formfield), coded as:
{=DropDown1+DropDown2+DropDown3+DropDown4+DropDown 5+DropDown6+DropDown7}
where the field braces (ie '{ }') are created via Ctrl-F9.


As for the TOC, the best way to update it in a form is to use a macro to temporarily unprotect the form, update the TOC, then
reprotect the form (with noreset = true).


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


"pedro gracio" wrote in ...
Hi,


This seems to work pretty well in word 2000 until it is used in a large document that has a table of contents. It seems to need
some sort of refresh.


I also tried this macro and although it also seems to work, it results in making the pages scroll up & down. This of course
would
be quite painful for the user.


Here's that code :


Sub AddDropDownResults()


Dim dDown1, dDown2, dDown3, dDown4, dDown5, dDown6, dDown7, As Integer
' Get value of first drop down form field.
dDown1 = Val(ActiveDocument.FormFields("DropDown1").Result)
' Get value of second drop down form field.
dDown2 = Val(ActiveDocument.FormFields("DropDown2").Result)
' Get value of thrid drop down form field.
dDown3 = Val(ActiveDocument.FormFields("DropDown3").Result)
' Get value of fouth drop down form field.
dDown4 = Val(ActiveDocument.FormFields("DropDown4").Result)
' Get value of fifth drop down form field.
dDown5 = Val(ActiveDocument.FormFields("DropDown5").Result)
' Get value of sixth drop down form field.
dDown6 = Val(ActiveDocument.FormFields("DropDown6").Result)
' Get value of seveth drop down form field.
dDown7 = Val(ActiveDocument.FormFields("DropDown7").Result)
' Calculate results and place in Text1 form field
ActiveDocument.FormFields("Text1").Result = Str(dDown1 + dDown2 + dDown3 + dDown4 + dDown5 + dDown6 + dDown7)


End Sub


Any help would be greatly appreciated


Thanks


Pedy- Hide quoted text -


- Show quoted text -


Hi macropod,

Sorry for re-posting, I did not see your reply...

How would I create a "macro to temporarily unprotect the form" ?

The other thing that I tried and it worked great until I placed the
table in a document with a TOC is just using a regular Text Form Field
type Number with the calculate on exit, and in the last cell I used
the formula "=SUM(d2:d21)". This again woked just fine, it calculated
automatically & all but once there is a TOC it didn't do anything...

pedy

  #12   Report Post  
Posted to microsoft.public.word.tables
rachid ayad rachid ayad is offline
external usenet poster
 
Posts: 1
Default how to rename macro in letter I just found icone Pleasehelp

how to rename macro in letter I just found icone Please help
Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
What is the formula to calculate a percentage in a Word table? J Kimble Tables 3 July 4th 23 12:37 PM
Calculate fields in a protected form e oman Tables 2 August 29th 07 01:30 PM
change color of cell based upon form drop down selection Tim Doyle Tables 1 October 27th 06 07:41 PM
How do I get form fields to calculate in Word? Mystified Mike Microsoft Word Help 1 December 18th 05 05:13 AM
formula to calculate form fill-in fields Gib Microsoft Word Help 1 February 5th 05 06:59 AM


All times are GMT +1. The time now is 07:57 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"