locked
Bookmarked Field Calculations Not Working RRS feed

  • Question

  • Hello, I saw a similar question, but it didn't quite address mine. I'm designing an evaluation form using three sections of drop down boxes of whole numbers within tables for the scores. At the bottom of each section, I have a formula that averages the above info to a 0.00 format. I don't like the fact that I have to hit F9 to calculate and wonder if there's a fix for that, but it does work.

    My meaty question is this: I am trying to insert an Overall Score at the top. I have bookmarked the formula fields as Performance1, Performance2, and Performance3. I have a single table box for the calculation field, and in it my formula looks like this: =AVERAGE(Performance1+Performance2+Performance3). The format is two decimal places.

    The formula shows 0.00 no matter what I do. Can anybody help me?  MANY THANKS!  


    Sunday, April 9, 2017 3:15 PM

Answers

  • You could do the whole lot quite easily using formfields instead of content controls, since formfields have a 'calculate on exit' property - which means the fields you're using for tallying scores & calculating averages would recalculate automatically. With content controls, a macro is required to automate the calculations.

    For a document demonstrating the use of formfields with formula fields for the calculations, see:
    http://www.msofficeforums.com/word/23773-calculating-values-drop-down-selections.html#post74823

    As demonstrated in the attachment to the post in the above link, you can indeed use formula fields to get the overall score; it's just a matter of getting the bookmarking and referencing right.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:37 PM
    Monday, April 10, 2017 2:00 AM
  • I told you the exact layout of my document

    ...

    I reviewed the form you sent me to reference again, and I see that it does have a calculation field. Unfortunately, it shows the same syntax errors I am getting, so it really doesn't provide the guidance I need.

    I doubt anyone would have guessed your document's 'exact layout' from the description you gave. All that was apparent to me was that it had a number of tables with varying rows containing content controls and that you wanted to tally each table and calculate an overall average somewhere.

    The attachment in the link I referred to does not produce any syntax errors when used as-is; it doesn't even have any field coding that might be affected by internationalisation issues. If you're getting such errors, it's because you changed something.

    What the field coding in that document shows is how you can tally the results from a range of dropdown formfields. The document link you posted for formfields (i.e. https://1drv.ms/w/s!AgHYerYS0aURhiVBDzHG68sj_VHa) will produce syntax errors while ever one of the formfields contributing to a calculation contains text (e.g. 'Score') - simply because you can't use text in a numeric calculation. Moreover, your little table with the 'Syntax error!' result contains four separate formula fields that are all input incorrectly, viz:
    { =AVERAGE(Goal1+Goal2+Goal3+Perf1+Perf2+Perf3+Perf4+Perf5+Perf6+Perf7+Sup1+Sup2+Sup3) \# "0.00" }
    { AVERAGE(Goal1Goal2Goal3Perf1Perf2Perf3Perf4Perf5Perf6Perf7Sup1Sup2Sup3) \# "0.00" }

    { AVERAGE(Perf1Perf2Perf3Perf4Perf5Perf6Perf7)= \# "0.00" }
    { AVERAGE(Performance1Performance2Performance3)= }

    Any formula field must start with '=' (which most of yours don't) and the arguments for an AVERAGE must be separated by spaces (which yours all lack), not + symbols.

    The document in the link I posted shows quite clearly how to reference the contents of a dropdown formfield. For 'Goal1', for example, you need to use a cross-reference field, which would look like {REF Goal1}. And to calculate the averages of all these fields, you would needed to use coding along the lines of:

    {=AVERAGE({REF Goal1} {REF Goal2} {REF Goal3} {REF Perf1} ... {REF Perf7} {REF Sup1} {REF Sup2} {REF Sup3}) \# 0.00}

    It's unclear whether even that is what you want, though, since another possibility is that you want the averages of the tables containing the Performance Indicators, Goal Achievement from Prior Review, and Supervisor Performance Indicators. In that case, you'd need to either bookmark the '{=AVERAGE(ABOVE) \# 0.00}' field in each table, then average those, or use a field coded along the lines of:

    {=AVERAGE({={REF Goal1}+{REF Goal2}+{REF Goal3}} {={REF Perf1}+...+{REF Perf7}} {={REF Sup1}+{REF Sup2}+{REF Sup3}}) \# 0.00}

    And, if you want to avoid the syntax errors caused by the default text, you can either:
    1. not have text in any of the dropdowns; or
    2. use additional field coding to test for such text, for example:
    instead of using just {REF Goal1} you might use:
    {IF{REF Goal1}= "SCORE" 0 {REF Goal1}}
    being aware, of course, that whatever numeric you use will impact the averages - unless you want to up the ante even more to eliminate such fields from the calculations altogether.

    Regarding the '{=AVERAGE(ABOVE) \# 0.00}' fields, do be aware that the 'ABOVE' parameter behaves inconsistently - it stops evaluating at the first non-numeric cell it encounters.

    To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
    http://windowssecrets.com/forums/showthread.php/154369-Microsoft-Word-Field-Maths-Tutorial
    or:
    http://www.gmayor.com/downloads.htm#Third_party

    PS: I note that the document in the link you posted for formfields (i.e. https://1drv.ms/w/s!AgHYerYS0aURhiVBDzHG68sj_VHa) still contains at least one content control. Content controls and formfields were not designed to work together and using both in the same document can lead to aberrant behaviour.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Monday, April 10, 2017 10:43 PM
  • Your averaging field in https://1drv.ms/w/s!AgHYerYS0aURhjfpKDCLgzurMAyk is coded as:

    { =AVERAGE({ Ref Goal1 }+{ REF Goal2 }+{ REF Goal3 }+{ REF Perf1 }+{ REF Perf2 }+{ REF Perf3 }+{ REF Perf4 }+{ REF Perf5 }+{ REF Perf6 }+ { REF Perf7 }+ { REF Sup1 }+{ REF Sup2 }+{ REF Sup3 } }

    with:

    ) \ # 0.00

    outside the field braces. Hence, part of the field code isn't even in the field! Plus the numeric picture switch should be \#, not \ #. Moreover, I thought my previous post was quite clear about NOT using + signs between the expressions to be averaged - + signs should only be used within an embedded formula field for groups that are to aggregated prior to averaging (as you started to do in https://1drv.ms/w/s!AgHYerYS0aURhjVcjRX_Z80sGP9W). Although I also suggested using spaces between the expressions, I now realise you should be using commas instead:

    {=AVERAGE({ Ref Goal1 },{ REF Goal2 },{ REF Goal3 },{ REF Perf1 },{ REF Perf2 },{ REF Perf3 },{ REF Perf4 },{ REF Perf5 },{ REF Perf6 },{ REF Perf7 },{ REF Sup1 },{ REF Sup2 },{ REF Sup3 }) \# 0.00}

    or:

    {=AVERAGE({={ Ref Goal1 }+{ REF Goal2 }+{ REF Goal3 }},{={ REF Perf1 }+{ REF Perf2 }+{ REF Perf3 }+{ REF Perf4 }+{ REF Perf5 }+{ REF Perf6 }+{ REF Perf7 }},{={ REF Sup1 }+{ REF Sup2 }+{ REF Sup3 }}) \# 0.00}

    Depending on the result required. The second result could just as easily be achieved via:

     {=AVERAGE({ Ref ScoreGoal },{ REF ScorePerf },{ REF ScoreSup }) \# 0.00}

    if you bookmark the 'Average' field accordingly in each of the tables concerned. Simply adding the bookmark to the cells containing those fields (as you've done in https://1drv.ms/w/s!AgHYerYS0aURhjfpKDCLgzurMAyk) is not enough.

    As for being unable to access the dropdowns, that's because you haven't applied the 'filling in forms' protection.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Tuesday, April 18, 2017 4:20 AM
    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Wednesday, April 12, 2017 2:15 AM
  • Your '0412a_Benedictine Evaluation_Draft for MP 4.12a' document still has two fields in the 'Overall Performance Rating' table:

    { =AVERAGE(Goal1,Goal2,Goal3,Perf1,Perf2,Perf3,Perf4,Perf5,Perf6,Perf7,Sup1,Sup2,Sup3) \# "0.00" }
    { AVERAGE(Performance1Performance2Performance3)= \# "0.00" }

    The second of these fields is unnecessary and cannot work.

    Even if you corrected that, however, the calculations won't work because the content controls are not bookmarked - all you have bookmarked is the empty paragraph before each content control - and because you have nothing to trigger any calculations (e.g. a macro).

    Your '0412b_Evaluation_Form Fields_Macro' document, also has two fields in the 'Overall Score' cell:
    {  }
    { =AVERAGE({ Ref ScoreGoal },{ REF ScorePerf },{ REF ScoreSup }) \#0.00 }
    the first of which is empty and should be deleted.

    As for the tables, themselves, you will need to replace the calculating formfields with fields coded as:
    {=AVERAGE({REF Goal1},{REF Goal2},{REF Goal3}) \# "0.00"}
    {=AVERAGE({REF Perf1},{REF Perf2},{REF Perf3},{REF Perf4},{REF Perf5},{REF Perf6},{REF Perf7}) \# "0.00"}
    {=AVERAGE({REF Sup1},{REF Sup2},{REF Sup3}) \# "0.00"}
    respectively, and bookmark these fields ScoreGoal, ScorePerf, and ScoreSup, respectively. Your table averages and 'Overall Score' calculation will then all work correctly.

    Finally, since this document doesn't require a macro, you may as well save it in the docx format (which will also delete its unnecessary code module).


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Tuesday, April 18, 2017 4:19 AM
    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Friday, April 14, 2017 8:21 AM
  • You have not bookmark fields ScoreGoal, ScorePerf, and ScoreSup, respectively - all you've done is to insert a bookmark before them. You must bookmark each entire field, excluding the end-of-cell marker.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Sunday, April 16, 2017 7:16 AM
  • So I have two questions: 1) How do I bookmark the calculation fields properly in this case? 2) Should the Ctrl F9 function be available within the form fields to utilize the field brackets? 2b) If so, do you know how I can access it?  

    https://1drv.ms/w/s!AgHYerYS0aURhl3rJtFMdUmR31V5

    1. Open the document and press Alt-F9 to expose all the field codes. Then select the compound field that is to have the ScoreGoal applied and use Insert|Boorkmark to add that bookmark. Do not include the end-of-cell marker. Selecting just the first character or two of the field code should be sufficient to select the entire field. Repeat for ScorePerf, and ScoreSup.

    2. No.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Monday, April 17, 2017 7:09 AM
  • Hmmm. I can't seem to select one or two characters without selecting the entire field

    That's the whole point of selecting the first few characters. The end-of-cell marker is the '¤'symbol that appears at the end of each cell when Word's formatting display is 'on'.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Monday, April 17, 2017 10:26 PM
  • GOD BLESS YOU! Thanks so much.
    • Marked as answer by climber43 Tuesday, April 18, 2017 3:02 AM
    Tuesday, April 18, 2017 1:48 AM

All replies

  • What kind of dropdowns are you using (formfields, content controls, activeX controls)? Are they in tables, or somewhere else? What about your formula fields - are they in tables, or somewhere else?

    You say you've "bookmarked the formula fields as Performance1, Performance2, and Performance3", but what would need to be bookmarked is the dropdowns. Bookmarking the formula fields won't help them calculate anything that's in the dropdowns.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, April 10, 2017 12:51 AM
  • Hi Paul,

    The dropdowns are content controls within tables. There are three columns, Description, Comments, Score. The first two are text and the third is the dropdown. Then, in the third column of the last row, I have a formula in the table. There are three sections like this. The formulas are formatted like this: AVERAGE(ABOVE). They work fine averaging the scores after numbers from the dropdowns are selected. 

    At the top of the first page, I have a table of text boxes for name, supervisor, etc. Then underneath that, I have text that reads Overall Score followed by a single cell table. Within that table is where I'm trying to do a formula that averages the scores from the three sections. 

    I think what you're saying is that I can't use the formula fields to get the overall score. I did try to bookmark each of the dropdown fields (testing the first section). I have tried different ways of formatting the formula, but it's still not showing a result. I've tried =SUM(Perf1,Perf2,Perf3…)/7 ( as there are 7 items within the first section), and I've tried =AVERAGE(Perf1+Perf2+Perf3…) and several other options. Can you tell me clearly how to format the formula if I have each of the dropdown bookmarked and want to average all of them? Sometimes I just get 0.00 and other times I get SYNTAX ERROR. I feel like I'm so close!

    MANY THANKS!!!

    Kim

    Monday, April 10, 2017 1:02 AM
  • You could do the whole lot quite easily using formfields instead of content controls, since formfields have a 'calculate on exit' property - which means the fields you're using for tallying scores & calculating averages would recalculate automatically. With content controls, a macro is required to automate the calculations.

    For a document demonstrating the use of formfields with formula fields for the calculations, see:
    http://www.msofficeforums.com/word/23773-calculating-values-drop-down-selections.html#post74823

    As demonstrated in the attachment to the post in the above link, you can indeed use formula fields to get the overall score; it's just a matter of getting the bookmarking and referencing right.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:37 PM
    Monday, April 10, 2017 2:00 AM
  • Hi again,

    Thanks. The link doesn't give me a document but another post series? I'm not finding how to do form fields. Is there an actual document with instructions?

    I'd really like to get this done, and I honestly don't have it in me to surf through more blah blah blah. I would like a straightforward HOW TO on this. 

    On the other hand, I'd be happy with knowing the right format for the formula to calculate the average of the drop downs. Would you be able to tell me that? Again, I feel like I've tried everything and am very close. Please? PLEASE?

    Monday, April 10, 2017 2:05 AM
  • 1. Please show correct formatting for Overall Rating field.

    2. Provide clear direction on form fields.

    3. Let me know how I can send you my actual document.

    THANKS!

    Monday, April 10, 2017 2:16 AM
  • The link contains an attached document using formfields and formula fields to do the kinds of calculations you've described in this thread. If you open the document and examine the fields and field coding, you'll see how it's all done.

    Since no-one here knows the exact layout of your document, it wouldn't be possible to tell you what the contents of any particular field should be, besides which, merely spoon-feeding you won't teach anything except how to be spoon-fed.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, April 10, 2017 4:01 AM
  • Wow. That wasn't at all condescending. I told you the exact layout of my document, and I also spelled out the many things I have tried. Spinning my wheels is not learning anything. I am not asking to be spoon fed. You suggested bookmarking the drop downs and using that to calculate, and I told you I had already tried that, I also spelled out a few of the ways I had tried to make it work.I asked for some help with the format of the formula. I appreciate the direction to the form fields, Unfortunately, it doesn't address the questions I asked. What I really need a way to calculate the average of the drop downs I already have. The form that's attached doesn't have a top overal score like what I"ve requested. I'm not asking to be spoon fed. I've looked everywhere and thought I could actually get some help here. I didn't need to be insutted. Would you be so kind as to offer me some assistance on how to format the formula? I don't think I need to change to form fields, as that doesn't seem to resolve my problem. Again, I"ve provided a great deal of information about my form, and I'll send it to you if you want to actually see it. I don't want to be spoon fed, but I would like a sharper knife.. I've assigned bookmarks to all my dropdowns and I just want to know how to format the formula to calculate the average. Do I use commas between? Plus signs? Can I average? I honestly can't find a solution and would reallly appreciate a straightforward answer instead of arrogance. Thanks so much.
    Monday, April 10, 2017 4:14 AM
  • Hello again,

    I reviewed the form you sent me to reference again, and I see that it does have a calculation field. Unfortunately, it shows the same syntax errors I am getting, so it really doesn't provide the guidance I need.

    I'll keep trying, but it would be lovely to get some direction that truly helps.

    Can somebody please help me? Thanks so much.

    Monday, April 10, 2017 2:09 PM
  • Wow. That wasn't at all condescending. ***

    It was not condescending in my opnion. Perhaps, it should have been. You need to grasp a few things.

    1. Paul knows more about Word field calculations than just about anyone, including people working for Microsoft.
    2. He does not work for Microsoft; nor do I.
    3. You are not paying him for his help, you are asking, and he is volunteering. He isn't being paid for this.
    4. It looks like you are asking him to do your job for you rather than help you do it.
    5. This is neither simple nor straightforward. It is something that can be done.
    6. That is likely to require time, thought, and work - by you. That is simply the nature of the beast.
    7. Tone it down.

    You can post a document to look at by putting it on OneDrive or Dropbox and then posting a link to it here.


    Charles Kenyon Madison, WI

    Monday, April 10, 2017 5:10 PM
  • Charles and Paul,

    Please accept my apology. I am truly not expecting anybody to do this for me. I thought I was clear in my request, and I have tried many things on my own and based on the responses I have seen. His response did feel condescending as I had provided a great deal of detail. I know you guys are experts and I sincerely appreciate your patience and assistance.

    Thank you for providing the info on posting a document. 

    Please find two versions of my form on OneDrive—one using bookmarks and one using form fields. The one with bookmarks should show two calculation fields with two different formats I had tried. As you will see, I'm struggling with the formula format for the Overall Score. As I mentioned in my first or second reply above, I have tried multiple formats, using different formula types including Average and Sum, and using commas, plus signs and nothing as bookmark separators. I also saw that Paul mentioned using CTRL F9 to insert brackets for a formula, and unfortunately, that didn't work either. 

    https://1drv.ms/w/s!AgHYerYS0aURhiddMzJzq9zkdZE0

    https://1drv.ms/w/s!AgHYerYS0aURhiVBDzHG68sj_VHa

    Again, many thanks. 

    Monday, April 10, 2017 5:26 PM
  • I told you the exact layout of my document

    ...

    I reviewed the form you sent me to reference again, and I see that it does have a calculation field. Unfortunately, it shows the same syntax errors I am getting, so it really doesn't provide the guidance I need.

    I doubt anyone would have guessed your document's 'exact layout' from the description you gave. All that was apparent to me was that it had a number of tables with varying rows containing content controls and that you wanted to tally each table and calculate an overall average somewhere.

    The attachment in the link I referred to does not produce any syntax errors when used as-is; it doesn't even have any field coding that might be affected by internationalisation issues. If you're getting such errors, it's because you changed something.

    What the field coding in that document shows is how you can tally the results from a range of dropdown formfields. The document link you posted for formfields (i.e. https://1drv.ms/w/s!AgHYerYS0aURhiVBDzHG68sj_VHa) will produce syntax errors while ever one of the formfields contributing to a calculation contains text (e.g. 'Score') - simply because you can't use text in a numeric calculation. Moreover, your little table with the 'Syntax error!' result contains four separate formula fields that are all input incorrectly, viz:
    { =AVERAGE(Goal1+Goal2+Goal3+Perf1+Perf2+Perf3+Perf4+Perf5+Perf6+Perf7+Sup1+Sup2+Sup3) \# "0.00" }
    { AVERAGE(Goal1Goal2Goal3Perf1Perf2Perf3Perf4Perf5Perf6Perf7Sup1Sup2Sup3) \# "0.00" }

    { AVERAGE(Perf1Perf2Perf3Perf4Perf5Perf6Perf7)= \# "0.00" }
    { AVERAGE(Performance1Performance2Performance3)= }

    Any formula field must start with '=' (which most of yours don't) and the arguments for an AVERAGE must be separated by spaces (which yours all lack), not + symbols.

    The document in the link I posted shows quite clearly how to reference the contents of a dropdown formfield. For 'Goal1', for example, you need to use a cross-reference field, which would look like {REF Goal1}. And to calculate the averages of all these fields, you would needed to use coding along the lines of:

    {=AVERAGE({REF Goal1} {REF Goal2} {REF Goal3} {REF Perf1} ... {REF Perf7} {REF Sup1} {REF Sup2} {REF Sup3}) \# 0.00}

    It's unclear whether even that is what you want, though, since another possibility is that you want the averages of the tables containing the Performance Indicators, Goal Achievement from Prior Review, and Supervisor Performance Indicators. In that case, you'd need to either bookmark the '{=AVERAGE(ABOVE) \# 0.00}' field in each table, then average those, or use a field coded along the lines of:

    {=AVERAGE({={REF Goal1}+{REF Goal2}+{REF Goal3}} {={REF Perf1}+...+{REF Perf7}} {={REF Sup1}+{REF Sup2}+{REF Sup3}}) \# 0.00}

    And, if you want to avoid the syntax errors caused by the default text, you can either:
    1. not have text in any of the dropdowns; or
    2. use additional field coding to test for such text, for example:
    instead of using just {REF Goal1} you might use:
    {IF{REF Goal1}= "SCORE" 0 {REF Goal1}}
    being aware, of course, that whatever numeric you use will impact the averages - unless you want to up the ante even more to eliminate such fields from the calculations altogether.

    Regarding the '{=AVERAGE(ABOVE) \# 0.00}' fields, do be aware that the 'ABOVE' parameter behaves inconsistently - it stops evaluating at the first non-numeric cell it encounters.

    To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
    http://windowssecrets.com/forums/showthread.php/154369-Microsoft-Word-Field-Maths-Tutorial
    or:
    http://www.gmayor.com/downloads.htm#Third_party

    PS: I note that the document in the link you posted for formfields (i.e. https://1drv.ms/w/s!AgHYerYS0aURhiVBDzHG68sj_VHa) still contains at least one content control. Content controls and formfields were not designed to work together and using both in the same document can lead to aberrant behaviour.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Monday, April 10, 2017 10:43 PM
  • Paul,

    Thank you so very much. My struggle was with formula formatting, and while I tried various combinations of =average(…) and =sum(…/x) with plus signs, commas, and spaces, I must not have gotten the right combination of the equal sign and spaces. I appreciate your providing that guidance. 

    Also, the Maths Tutorial is splendid and I'm sure I will gain a lot more insight from that. 

    Thanks for the thorough explanation. It is greatly appreciated.

    Cheers to you, 

    Monday, April 10, 2017 11:46 PM
  • I'm so sorry—it's me again.<o:p></o:p>

    My intent is in fact to provide an overall score, which is the average of the three tables. I thought I understood from your first response that I wouldn't be able to use the =average(ABOVE) formula fields but would have to bookmark the individual fields above, but that was before I tried switching to form fields. <o:p></o:p>

    Can I ask you to please take a look at the two documents I have posted here:<o:p></o:p>

    https://1drv.ms/w/s!AgHYerYS0aURhjfpKDCLgzurMAyk<o:p></o:p>

    https://1drv.ms/w/s!AgHYerYS0aURhjVcjRX_Z80sGP9W<o:p></o:p>

    Form Fields_0411a — I eliminated the content controls, removed the word "Score" from the drop downs, and set up an average formula using the bookmarked fields as shown above. I utilized CTRL F9 to get the {, and I think I have the close } and ) in the right place. <o:p></o:p>

    {=AVERAGE({={REF Goal1}+{REF Goal2}+{REF Goal3}} {={REF Perf1}+...+{REF Perf7}} {={REF Sup1}+{REF Sup2}+{REF Sup3}}) \# 0.00}<o:p></o:p>

    Form Fields_0411b — I eliminated the content controls, removed the word "Score" from the drop downs, bookmarked the =AVERAGE(ABOVE) fields, and set up an average formula in the Overall Score table field at the top using those bookmarks: <o:p></o:p>

    {=AVERAGE({={REF ScorePerf}+{REF ScoreGoal}+{REF ScoreSup}}}) \# 0.00}<o:p></o:p>

    I still get syntax errors. <o:p></o:p>

    Also, it seems that I'm not able to access the dropdowns in either case. <o:p></o:p>

    I'm so sorry to continue to trouble you.<o:p></o:p>

    Tuesday, April 11, 2017 5:18 PM
  • Your averaging field in https://1drv.ms/w/s!AgHYerYS0aURhjfpKDCLgzurMAyk is coded as:

    { =AVERAGE({ Ref Goal1 }+{ REF Goal2 }+{ REF Goal3 }+{ REF Perf1 }+{ REF Perf2 }+{ REF Perf3 }+{ REF Perf4 }+{ REF Perf5 }+{ REF Perf6 }+ { REF Perf7 }+ { REF Sup1 }+{ REF Sup2 }+{ REF Sup3 } }

    with:

    ) \ # 0.00

    outside the field braces. Hence, part of the field code isn't even in the field! Plus the numeric picture switch should be \#, not \ #. Moreover, I thought my previous post was quite clear about NOT using + signs between the expressions to be averaged - + signs should only be used within an embedded formula field for groups that are to aggregated prior to averaging (as you started to do in https://1drv.ms/w/s!AgHYerYS0aURhjVcjRX_Z80sGP9W). Although I also suggested using spaces between the expressions, I now realise you should be using commas instead:

    {=AVERAGE({ Ref Goal1 },{ REF Goal2 },{ REF Goal3 },{ REF Perf1 },{ REF Perf2 },{ REF Perf3 },{ REF Perf4 },{ REF Perf5 },{ REF Perf6 },{ REF Perf7 },{ REF Sup1 },{ REF Sup2 },{ REF Sup3 }) \# 0.00}

    or:

    {=AVERAGE({={ Ref Goal1 }+{ REF Goal2 }+{ REF Goal3 }},{={ REF Perf1 }+{ REF Perf2 }+{ REF Perf3 }+{ REF Perf4 }+{ REF Perf5 }+{ REF Perf6 }+{ REF Perf7 }},{={ REF Sup1 }+{ REF Sup2 }+{ REF Sup3 }}) \# 0.00}

    Depending on the result required. The second result could just as easily be achieved via:

     {=AVERAGE({ Ref ScoreGoal },{ REF ScorePerf },{ REF ScoreSup }) \# 0.00}

    if you bookmark the 'Average' field accordingly in each of the tables concerned. Simply adding the bookmark to the cells containing those fields (as you've done in https://1drv.ms/w/s!AgHYerYS0aURhjfpKDCLgzurMAyk) is not enough.

    As for being unable to access the dropdowns, that's because you haven't applied the 'filling in forms' protection.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Tuesday, April 18, 2017 4:20 AM
    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Wednesday, April 12, 2017 2:15 AM
  • Last time…I promise.

    In my latest form, I have bookmarked the average fields in each of the tables and I have all the fields set with "calculate on exit." I moved the Overall Score from its own little table into the top table and updated the formula as suggested to  {=AVERAGE({ Ref ScoreGoal },{ REF ScorePerf },{ REF ScoreSup }) \# 0.00} with the number format inside the bracket and a space after the backslash. When I protect the form and select options from the drop-downs, I don't get a result in the =AVERAGE(ABOVE) field, so the top formula remains 0.00. It seems like I got the top formula correct, but something is still/now wrong with the =AVERAGE(ABOVE) fields. 

    https://1drv.ms/f/s!AgHYerYS0aURhlj1axrYztu_d1lR

    In addition, I redid an older version with content controls and a macro to update all formulas. On this one, all the =AVERAGE(ABOVE) fields calculate, but the OVERALL SCORE does not.

    In both cases, I experimented with commas and spaces and different groups of parenthesis again. 

    Thank you. You've been most helpful.

    climber43


    Thursday, April 13, 2017 1:20 PM
  • Your '0412a_Benedictine Evaluation_Draft for MP 4.12a' document still has two fields in the 'Overall Performance Rating' table:

    { =AVERAGE(Goal1,Goal2,Goal3,Perf1,Perf2,Perf3,Perf4,Perf5,Perf6,Perf7,Sup1,Sup2,Sup3) \# "0.00" }
    { AVERAGE(Performance1Performance2Performance3)= \# "0.00" }

    The second of these fields is unnecessary and cannot work.

    Even if you corrected that, however, the calculations won't work because the content controls are not bookmarked - all you have bookmarked is the empty paragraph before each content control - and because you have nothing to trigger any calculations (e.g. a macro).

    Your '0412b_Evaluation_Form Fields_Macro' document, also has two fields in the 'Overall Score' cell:
    {  }
    { =AVERAGE({ Ref ScoreGoal },{ REF ScorePerf },{ REF ScoreSup }) \#0.00 }
    the first of which is empty and should be deleted.

    As for the tables, themselves, you will need to replace the calculating formfields with fields coded as:
    {=AVERAGE({REF Goal1},{REF Goal2},{REF Goal3}) \# "0.00"}
    {=AVERAGE({REF Perf1},{REF Perf2},{REF Perf3},{REF Perf4},{REF Perf5},{REF Perf6},{REF Perf7}) \# "0.00"}
    {=AVERAGE({REF Sup1},{REF Sup2},{REF Sup3}) \# "0.00"}
    respectively, and bookmark these fields ScoreGoal, ScorePerf, and ScoreSup, respectively. Your table averages and 'Overall Score' calculation will then all work correctly.

    Finally, since this document doesn't require a macro, you may as well save it in the docx format (which will also delete its unnecessary code module).


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Tuesday, April 18, 2017 4:19 AM
    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Friday, April 14, 2017 8:21 AM
  • I see. I have removed the extraneous field and the macro (didn't realize either was in there—thanks for letting me know). 

    Within each section, I believe I have the average calculation fields are bookmarked and coded correctly—as I have checked and rechecked them. They are working well.

    The overall score is still not working. I am entering it as a form field (using CTRL F9 as opposed to Table/Formula). I have tried exactly as you show above. I have also tried the number format inside quotes and without quotes (you referenced both). I have put a space before the \ and removed the space. I have made sure the bookmarks match the formulas. I also tried using Developer/Legacy Tools/Text Box, then updating the properties. Of course, CTRL F9 isn't enabled in that format. I feel like Sam I Am's friend from Green Eggs & Ham. I feel like I have tried it in a boat, with a goat, on a train, with Mickey Spillane, with a fox, and wearing no socks. :-) The right combination continues to elude me.

    https://1drv.ms/w/s!AgHYerYS0aURhl3rJtFMdUmR31V5

    I wish you a happy Easter and hope you see something else I'm doing wrong. 

    Saturday, April 15, 2017 9:23 PM
  • You have not bookmark fields ScoreGoal, ScorePerf, and ScoreSup, respectively - all you've done is to insert a bookmark before them. You must bookmark each entire field, excluding the end-of-cell marker.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Sunday, April 16, 2017 7:16 AM
  • I hear you on the bookmarking, and therein lies my dilemma. I think.

    As I mentioned in a previous reply, I did all the drop-downs as form fields and bookmarked them accordingly. (Developer/Legacy Form Fields—then updated properties with the bookmark). See first screen shot here.

    https://1drv.ms/w/s!AgHYerYS0aURhmdBKtWYxrNqAvoK

    When doing the calculation fields, however, I tried to do them the same way. Unfortunately, I found that I could not use CTRL F9 within the form field properties in order to get the field brackets. So then I simply entered the code in the table cell. Having no bookmark access within the field options, I tried to insert a bookmark. (Insert/Bookmark). I see what you mean about inserting a bookmark before them. I have tried to highlight the entire cell and insert the bookmark that way, but it doesn't work. See second and third screen shot.

    So I have two questions: 1) How do I bookmark the calculation fields properly in this case? 2) Should the Ctrl F9 function be available within the form fields to utilize the field brackets? 2b) If so, do you know how I can access it?  

    https://1drv.ms/w/s!AgHYerYS0aURhl3rJtFMdUmR31V5

    Monday, April 17, 2017 3:33 AM
  • So I have two questions: 1) How do I bookmark the calculation fields properly in this case? 2) Should the Ctrl F9 function be available within the form fields to utilize the field brackets? 2b) If so, do you know how I can access it?  

    https://1drv.ms/w/s!AgHYerYS0aURhl3rJtFMdUmR31V5

    1. Open the document and press Alt-F9 to expose all the field codes. Then select the compound field that is to have the ScoreGoal applied and use Insert|Boorkmark to add that bookmark. Do not include the end-of-cell marker. Selecting just the first character or two of the field code should be sufficient to select the entire field. Repeat for ScorePerf, and ScoreSup.

    2. No.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Monday, April 17, 2017 7:09 AM
  • Hmmm. I can't seem to select one or two characters without selecting the entire field. Have tried repeatedly. By "end of cell marker" do you mean the last } ?

    https://1drv.ms/w/s!AgHYerYS0aURhl3rJtFMdUmR31V5

    Monday, April 17, 2017 12:48 PM
  • Hmmm. I can't seem to select one or two characters without selecting the entire field

    That's the whole point of selecting the first few characters. The end-of-cell marker is the '¤'symbol that appears at the end of each cell when Word's formatting display is 'on'.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by climber43 Tuesday, April 18, 2017 6:38 PM
    Monday, April 17, 2017 10:26 PM
  • Yeah, I get it. I just can't make it happen. Thanks, though.
    Monday, April 17, 2017 11:12 PM
  • Do you know of anyone who could make this change for me? My client will pay for it. Thanks again.
    Tuesday, April 18, 2017 12:43 AM
  • I have edited your latest OneDrive document (https://1drv.ms/w/s!AgHYerYS0aURhl3rJtFMdUmR31V5) and applied 'filling in forms' protection. It now works as it should:

    1. Bookmarks corrected
    2. Field switch corrected for the 'Overall Score' field
    3. Missing operator (a comma) supplied for one of the other 'Average' fields.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Tuesday, April 18, 2017 4:19 AM
    Tuesday, April 18, 2017 1:24 AM
  • GOD BLESS YOU! Thanks so much.
    • Marked as answer by climber43 Tuesday, April 18, 2017 3:02 AM
    Tuesday, April 18, 2017 1:48 AM
  • Thanks Paul for the great help.

    Hi climber43,

    Do you mind to mark Paul's relies above as answers, so that other community members will get a clear view of the useful ones?

    Thanks for your great understanding.

    Regards,

    Ethan Hua


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, April 18, 2017 7:38 AM
  • Sure. However, I have one last problem. While I can fill in the form fields, I can't type in the Comments fields with the protection on. I guess I will make them form fields.

    Thanks!


    climber43

    Tuesday, April 18, 2017 12:28 PM
  • Yes, make them form fields.

    Monday, April 24, 2017 12:42 PM
  • I have a related question to this string and didn't want to start a new topic. 

    is it possible to include "NA" as a dropdown option in a tabled form field and have the calculation field work? 

    I am adapting an existing form for a client in hopes of making it more flexible for them. There are currently 6 questions within a table. Each has dropdowns including numbers 1-5; we want to add NA in case the question doesn't apply.  

    I added NA to the existing dropdown lists for each question, but then when I went to test the form, I found that the average field would return "!Undefined Bookmark, NA" error message if I selected NA as response to any question.

    It seems like this should work. Any suggestions? We don't want to have two separate forms. 

    Thanks in advance.



    climber43

    Sunday, May 14, 2017 1:40 AM
  • That could require extensive recoding of the fields (I can no longer access the one I uploaded to your OneDrive folder last month to see the fields, but I believe it used formulae that tallied their values, then divided by the count of non-zero fields; obviously, trying to tally fields containing 'N/A' isn't going to work), but you could probably get the same result by making 0 an option instead of N/A.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, May 14, 2017 12:03 PM
  • Thanks. I am sharing the file again. The formulae averaged the numbers in the table above. For the Goals section, the employees being evaluated have previously set 2-3 goals. I set up three fields, but if the employee had only 2 goals, one would not apply. Hence the need for the NA selection. (Currently I have the NA option only in the last question, but you will see the error.)

    https://1drv.ms/w/s!AgHYerYS0aURh1cjoid7BD7K2ekX

    Same for the Supervisor Performance Indicators. If the employee isn't a supervisor, none of the questions in this section will apply. I could make another form with that section removed, but the Goals problem remains, and we also already have a separate Leadership form. I could ask the evaluating managers to unprotect the form anddelete the non-applicable sections, but of course I want this to be as use friendly as possible.

    I'm afraid including 0 as an option will only reduce the overall score (as the calculation field is an average). Excel will average non-numeric fields, but of course I know this is different. 

    What kind of "extensive recoding" would be needed? Please let me know if you see a solution.

    Many thanks.


    climber43

    Sunday, May 14, 2017 1:43 PM
  • As indicated, substantial recoding is required; the formula field for the Goal Achievements becomes:

    {IF{={IF{REF Goal1}= "NA" 0 1}+{IF{REF Goal2}= "NA" 0 1}+{IF{REF Goal3}= "NA" 0 1}}= 0 0 {=({IF{REF Goal1}= "NA" 0 {REF Goal1}}+{IF{REF Goal2}= "NA" 0 {REF Goal2}}+{IF{REF Goal3}= "NA" 0 {REF Goal3}})/({IF{REF Goal1}= "NA" 0 1}+{IF{REF Goal2}= "NA" 0 1}+{IF{REF Goal3}= "NA" 0 1})} \# "0.00"}


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Tuesday, May 16, 2017 1:32 AM
    Monday, May 15, 2017 5:45 AM
  • Thank you so much. That works excellently. I am not quite sure I understand the coding, however, and I would very much like to so I can apply the logic in future endeavors.

    Best regards,



    climber43

    Monday, May 15, 2017 2:27 PM
  • Also, I added a 4th goal option and updated the coding accordingly. I am missing an operator, but I can't find it. I think I have my + and = and ( and { in the right place for each set of arguments, but clearly I missed something. Thanks again.

    https://1drv.ms/w/s!AgHYerYS0aURh1sih-M9Mz4HxneM


    climber43

    Monday, May 15, 2017 2:41 PM
  • I apologize for several replies here, but I am summarizing everything in this one:

    The coding works great—thank you!

    However, I added a 4th goal option and updated the coding accordingly. I am missing an operator, but I can't find it. I think I have my + and = and ( and { in the right place for each set of arguments, but clearly I missed something. Do you see what operator I've missed?

    https://1drv.ms/w/s!AgHYerYS0aURh1sih-M9Mz4HxneM

    My client has just advised that they want spell check enabled in the comments fields (currently form fields). From what I've found, the only way to enable spell check on a protected form like this is to add a macro. Is that correct?

    They want to be able to use bullet point formatting in the comment fields as well, and the same thing appears to apply for that. I'm very interested in a solution to these essentially basic functions that disappear when a form is protected.

    These last two questions probably belong in other threads (I found some info from you as well, Paul), but as they pertain to this same form, I'm posing them here. 

    Thanks SO much.


    climber43

    Monday, May 15, 2017 10:47 PM
  • The revised code tests each bookmark for 'NA', as in {IF{REF Goal1}= "NA" 0 1} and treats them as 0s; anything else is treated as 1. The first IF test sums this for all three bookmarks:
    {IF{={IF{REF Goal1}= "NA" 0 1}+{IF{REF Goal2}= "NA" 0 1}+{IF{REF Goal3}= "NA" 0 1}}= 0 0
    and, if the sum is 0, outputs the zero instead of trying to calculate an average.

    Assuming a non-zero result for the above test, the average is calculated. Again, the code tests each bookmark for 'NA' but, whereas the previous test output 1 for anything other than 'NA', the actual field result is used for the numerator:
    ({IF{REF Goal1}= "NA" 0 {REF Goal1}}+{IF{REF Goal2}= "NA" 0 {REF Goal2}}+{IF{REF Goal3}= "NA" 0 {REF Goal3}})
    For the denominator, its the same 0 or 1 output as before:
    ({IF{REF Goal1}= "NA" 0 1}+{IF{REF Goal2}= "NA" 0 1}+{IF{REF Goal3}= "NA" 0 1})
    The average is simply the sum of the numerators divided by the sum of the denominators.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Tuesday, May 16, 2017 1:33 AM
    Monday, May 15, 2017 11:41 PM
  • Great - thanks. That makes sense.

    I resolved the coding after I added the 4th goal and am adding a macro to enable spell check. Still haven't found a viable solution to enable bullet points. 


    climber43

    Tuesday, May 16, 2017 2:01 PM
  • Still haven't found a viable solution to enable bullet points.

    About the only way you could do that in your document would be to replace the formfields in the Comments columns with Rich Text Content Controls.

    I wouldn't ordinarily recommend that, though, as content controls and formfields weren't designed to work together and documents can behave inconsistently when they're combined in the one document. One thing you'd find, for example, is that although you can tab out of a formfield and be taken to either a formfield or a content control, depending on what's next, tabbing out of a content control will only take you to another content control.

    Of course, had I known you planned to enable spell checking from the outset, I would probably have suggested a macro-based solution with content controls for all of the processing instead of using formfields and formula fields. That way, you wouldn't even need document forms protection...


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, May 16, 2017 10:39 PM
  • Thanks - Yes, I had no idea spell check or bullet points would be required, and in fact it wouldn't have occurred to me that those wouldn't work after the form was protected. I have been playing with it a bit and will keep doing that taking what you've said into account. I thought of trying the content controls but remembered that they don't play nicely with form fields. I will see how that works. 

    I'll let you know what I figure out. Thanks again!



    climber43

    Tuesday, May 16, 2017 11:30 PM
  • OK, I have added a macro to spell check on exit. It works if I unprotect the form and manually run it, but of course that defeats the purpose. It is not running on exit like it's supposed to. Would you have a quick look and see if you can tell me what's wrong?

    https://1drv.ms/w/s!AgHYerYS0aURh2_IY_jpuqSt8PAB


    climber43

    Wednesday, May 17, 2017 12:30 PM
  • Thanks. That's actually what I used. Something isn't working properly though.

    climber43

    Wednesday, May 17, 2017 12:41 PM
  • OK, I have added a macro to spell check on exit. It works if I unprotect the form and manually run it, but of course that defeats the purpose. It is not running on exit like it's supposed to. Would you have a quick look and see if you can tell me what's wrong?

    https://1drv.ms/w/s!AgHYerYS0aURh2_IY_jpuqSt8PAB


    climber43

    This is very much off the topic of this thread. I would suggest, first, looking at How to enable the spellchecker in a protected document. If that does not work for you, post a new question.

    Charles Kenyon Madison, WI

    Wednesday, May 17, 2017 12:44 PM
  • Again, apologies for the previous off-topic question. Have addressed the spell check macro in a more appropriate place.

    Back to this topic, however, I have a new problem. The coding Paul provided for the NA options works, and I utilized that both in the Goals and Supervisor sections, replicating the Supervisor section like this {IF{={IF{REF Sup1}= "NA" 0 1}+{IF{REF Sup2}= "NA" 0 1}+{IF{REF Sup3}= "NA" 0 1}}= 0 0 {=({IF{REF Sup1}= "NA" 0 {REF Sup1}}+{IF{REF Sup2}= "NA" 0 {REF Sup2}}+{IF{REF Sup3}= "NA" 0 {REF Sup3}})/({IF{REF Sup1}= "NA" 0 1}+{IF{REF Sup2}= "NA" 0 1}+{IF{REF Sup3}= "NA" 0 1})} \# "0.00"}

    In the Goals section, there will always be at least 2 numbers selected (and possibly 2 NA responses as they can have 2-4 goals to score), so there will always be a number returned that can be averaged in with the Overall Score without affecting the total, so no problem there. In the Supervisor section, however, if the employee being evaluated is not a supervisor, all responses would be NA, and the calculation will return a zero. That won't work for the overall score.

    I have strongly suggested simply having a separate form for supervisors so this isn't a factor, but I did want to ask if there is a way around it. Thanks for your help.

    Thanks again. 


    climber43

    Thursday, May 18, 2017 9:04 PM
  • Here's the form…

    https://1drv.ms/w/s!AgHYerYS0aURiAP2A9_5pig6d90b


    climber43

    Thursday, May 18, 2017 9:09 PM
  • In the Goals section, there will always be at least 2 numbers selected (and possibly 2 NA responses as they can have 2-4 goals to score), so there will always be a number returned that can be averaged in with the Overall Score without affecting the total, so no problem there. In the Supervisor section, however, if the employee being evaluated is not a supervisor, all responses would be NA, and the calculation will return a zero. That won't work for the overall score.

    So what should the 'overall' score show if the Supervisor score is 0?

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, May 18, 2017 10:43 PM
  • Overall score should return Performance Indicator Score if Supervisor = zero. (They aren't scoring goals this time)

    climber43

    Thursday, May 18, 2017 10:47 PM
  • In that case, you need to adopt the same approach for the overall score as you've adopted for the individual scores, except for there being no NA results to parse:

    {IF{={REF ScoreGoal}+{REF ScorePerf}+{REF ScoreSup}}= 0 0 {=({REF ScoreGoal}+{REF ScorePerf}+{REF ScoreSup})/({IF{REF ScoreGoal}= 0 0 1}+{IF{REF ScorePerf}= 0 0 1}+{IF{REF ScoreSup}= 0 0 1})} \# "0.00"}

    This will also give a more accurate result should the Goals score be 0.


    Cheers
    Paul Edstein
    [MS MVP - Word]


    Thursday, May 18, 2017 11:08 PM
  • Gotcha—thanks. I was trying to piece together the conditions. This helps a ton.

    Meanwhile, I included the form here unprotected with the coding exposed. When I get back in and protect, the Supervisor calculation is no longer working. It just blanks out. I just reviewed everything and it looks right, but I must have deleted something. My eyes aren't finding it. Would you have a quick look? 

    This form is going to kill me.  :)


    climber43

    Thursday, May 18, 2017 11:14 PM
  • Your latest attachment is missing all the prior review scores, along with the Goal bookmarks related thereto. Obviously, if the overall score calculation references a deleted bookmark, it's going to fail. Equally, it's hard to provide the appropriate field codes when the goalposts keep moving. The formula there should now become:

    {IF{={REF ScorePerf}+{REF ScoreSup}}= 0 0 {=({REF ScorePerf}+{REF ScoreSup})/({IF{REF ScorePerf}= 0 0 1}+{IF{REF ScoreSup}= 0 0 1})} \# "0.00"}

    As for the supervisor calculation, your formula field is incorrect and the first formfield contributing to its calculation doesn't have the 'calculate on exit' option checked. The supervisor calculation formula should be:

    {IF{={IF{REF Sup1}= "NA" 0 1}+{IF{REF Sup2}= "NA" 0 1}+{IF{REF Sup3}= "NA" 0 1}+{IF{REF Sup4}= "NA" 0 1}}= 0 0 {=({IF{REF Sup1}= "NA" 0 {REF Sup1}}+{IF{REF Sup2}= "NA" 0 {REF Sup2}}+{IF{REF Sup3}= "NA" 0 {REF Sup3}}+{IF{REF Sup4}= "NA" 0 {REF Sup4}})/({IF{REF Sup1}= "NA" 0 1}+{IF{REF Sup2}= "NA" 0 1}+{IF{REF Sup3}= "NA" 0 1}+{IF{REF Sup4}= "NA" 0 1})} \# "0.00"}

    I'd have edited your OneDrive document, but you have it locked for editing.

    PS: For a macro to convert the text representations of the field codes to working field codes, see Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks thread at:
    http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
    or:
    http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks


    Cheers
    Paul Edstein
    [MS MVP - Word]




    • Edited by macropodMVP Friday, May 19, 2017 2:00 AM Added PS
    Friday, May 19, 2017 1:52 AM
  • Tell me about shifting goal posts!  This has been one big project of shifting sands.  :) Yep, client decided they don't want to score the goals for this one. But I'm sure they will, so I have that coding tucked away…

    Thanks again. I'm not sure why the document was locked for editing. I must have copied a read only link. Here it is again. I added the "calculate on exit" to the Sup1 field. I have meticulously coded (and I understand what I'm doing!) based on what you have above, both the overall score and the supervisor score fields. Unfortunately, the Supervisor score is blanking out when I am done so of course the overall score is returning a syntax error.  

    https://1drv.ms/w/s!AgHYerYS0aURiAXeqEkyZlbCAcZD



    climber43

    Friday, May 19, 2017 3:56 AM
  • I've fixed your field coding. It works now.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, May 19, 2017 6:26 AM
  • Excellent! I better get it to my client fast before they want something else changed! THANKS!

    climber43

    Friday, May 19, 2017 1:45 PM