Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.tables
|
|||
|
|||
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. |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
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. |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.tables
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the formula to calculate a percentage in a Word table? | Tables | |||
Calculate fields in a protected form | Tables | |||
change color of cell based upon form drop down selection | Tables | |||
How do I get form fields to calculate in Word? | Microsoft Word Help | |||
formula to calculate form fill-in fields | Microsoft Word Help |