locked
Calculate All (F9) not updating all Fields in Document RRS feed

  • Question

  • I have a document template used for presenting quotations in which I have set up two tables

    1. Itemising Fixed Price Items
    2. Provisional Priced Items

    At the bottom of each table I have used the "sum(above)" to total the values in the last column of each table and bookmarked these values as 

    1. TotalFixedSum
    2. TotalProvSum

    If I place a calculated sum to total the two tables before the tables then the sum only includes the sum of the first table when I use the Ctrl-A and F9 function but does sum the two tables if I use the manual update or F9 function on the specific calculated field

    Calculated field formula {=if(defined(TotalProvSum), TotalFixedSum+TotalProvSum, TotalFixedSum) \# "$#,##0.00;($#,##0.00)"

    The calculated field after the two tables is always correct, irrespective of using the Ctrl-A and F9 or manual update on the field.

    The reason for the check  [defined] on the Provisional Sum is that sometimes the user will generate a quotation that doesn't have any provisional sums and will delete the table used for provisional items.

    The reason for displaying the calculated total before the tables is that we present a Summary page preceding the detailed quotation

    Can you please advise how I can otherwise make the F9 function work so that I can be assured the document is displaying the correct values both in the Summary page and the bottom of the detailed pages?

    Thank you in advance

    Tuesday, November 19, 2019 1:52 AM

Answers

  • Just as observations...

    ...As a general rule, Word (a) calculates field results starting at the beginning of the selection and proceeding to the end, and uses the values of bookmarks as "most recently calculated".

    So until Word gets to the end of the selection and has calculated all the bookmark values in the selection, it may be using "old" bookmark values when doing earlier calculations.

    That means that however you make a "forward reference," i.e. a reference to a result that is created further down the document, the value of that forward reference may not be correct until you have updated all the fields twice.

    I don't actually think there is a simple way around that - the simplest way is probably not to make forward references, as you have tried. Trying to persuade your user(s) to update the values twice is another way.

    You can see a crude example of this if you enter the following fields 

    { =bookmark1 }
    { SET bookmark1 1 }

    and avoid updating the SET field.

    The first time you ctrl-A, F9, you will see something like

    !Undefined Bookmark, BOOKMARK1

    because the bookmark hasn't been created. Next time you should see 

    1

    ...I don't think the DEFINED function will help you here, as you have discovered, but actually I would avoid using DEFINED() anyway for a number of reasons.

    Reason 1 is that you cannot pass any old parameter to DEFINED() and have it return 1 or 0. It can also return various kinds of field error. A simple example is if you try

    { =DEFINED(1/0) }

    Reason 2 is that the behaviour of DEFINED when you reference a bookmark in its parameter varies depending on exactly how you defined the bookmark.

    In your situation, in effect you had this...

    { =DEFINED(TotalProvSum) }

    And further down the document in a table you had something like

    { =SUM(ABOVE) }

    and you had applied the bookmark TotalProvSum to that field or even the entire cell it is in (I think)

    In *that* case, when you select the text from the DEFINED field to the SUM field and press F9, what Word seems to do is start by undefining TotalProvSum. So { =DEFINED(TotalProvSum) } returns 0. The same thing happens when you use ctrl-A, F9.

    However if in that table you did this:

    { SET TotalProvSum { =SUM(ABOVE) } } { TotalProvSum }

    and selected the same material and updated the fields (or ctrl-A, F9),

    { =DEFINED(TotlProvSum) } returns 1.

    Finally, if you just have a piece of text containing a value such as 123.45 and bookmark it with TotalProvSum, and select the fields/bookmarked text in a similar way, { =DEFINED(TotalProvSum) } returns 1.

    There's more, but IMO that's already enough to suggest that this isn't a function anyone should rely on unless they are very sure they have tested it adequately for their use case.



    Peter Jamieson


    • Marked as answer by gbkiwi Thursday, November 21, 2019 8:44 AM
    • Edited by Peter Jamieson Thursday, November 21, 2019 12:02 PM
    Wednesday, November 20, 2019 8:19 PM
  • Hi,

    Based on your description, I could reproduce the issue you are encountering:


    Generally, we can follow the methods in Use a formula in a Word or Outlook table and Update fields to update all fields in the document. We can press Ctrl + A and then press F9 to update all fields in a document.

    In addition, you can use VBA to do this:

    Sub UpdateAllFields()
        ActiveDocument.Fields.Update
    End Sub

    Word inserts cross-references as fields. If a cross-reference doesn't reflect changes you make in the bookmark it refers to, update the field. Right-click the field code, and then click Update Field on the shortcut menu. 

    From my point of view, the issue may be related to the behavior of Word's calculation fields referring to bookmark  when update all fields. I suggest you manually update the fields before these table after updating all fields in the documents.

    If you have any updates, feel free to post back to let us know.

    Best Regards,

    Herb


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

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    • Marked as answer by gbkiwi Saturday, November 23, 2019 6:09 PM
    Tuesday, November 19, 2019 10:23 AM

All replies

  • Hi,

    Based on your description, I could reproduce the issue you are encountering:


    Generally, we can follow the methods in Use a formula in a Word or Outlook table and Update fields to update all fields in the document. We can press Ctrl + A and then press F9 to update all fields in a document.

    In addition, you can use VBA to do this:

    Sub UpdateAllFields()
        ActiveDocument.Fields.Update
    End Sub

    Word inserts cross-references as fields. If a cross-reference doesn't reflect changes you make in the bookmark it refers to, update the field. Right-click the field code, and then click Update Field on the shortcut menu. 

    From my point of view, the issue may be related to the behavior of Word's calculation fields referring to bookmark  when update all fields. I suggest you manually update the fields before these table after updating all fields in the documents.

    If you have any updates, feel free to post back to let us know.

    Best Regards,

    Herb


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

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    • Marked as answer by gbkiwi Saturday, November 23, 2019 6:09 PM
    Tuesday, November 19, 2019 10:23 AM
  • Thanks Herb for replicating the problem and verifying the behaviour.

    However my challenge is that I am trying to leave my client/colleagues (who are not greatly familiar with finding their way around macros and treat such solutions as a black art) so have tried to keep the template to very simple structures, allowing them to edit and not have to delve into formulas etc.. They are good with inserting new lines in tables and not leaving blank cells in the totals column and that about covers it.

    Currently if they manually update the fields prior to the tables, it is all ok, but if they then update the tables and inadvertently do a Ctrl A & F9, the prior calculated field reverts to only including the first tables sum and treating the second tables sum as zero.

    My only fail safe solution that I can think of is to place the Summary page after the Details and then reposition the printed page in the collated document when presenting it to the customer.

    Cheers

    Richard

    Tuesday, November 19, 2019 7:46 PM
  • Hi All

    I think I may have stumbled on the solution. By referencing the bookmark (TotalProvSum) on its own in the Summary page, without using the "defined" function, it seems to establish the valuation which can then be used thereafter. A sort of declarative function I suppose.

    As it so happens if the table of Provisional items is deleted then then the user would also delete the Total of Provisional Items line from the Summary Page and the Total Sum will stay and work correctly by failing to find defined bookmark TotalProvSum.

    So All Good

    Thanks

     


    Tuesday, November 19, 2019 8:38 PM
  • Hi,

    I'm glad to see that you have found the right direction to solve the problem. 

    Would you please mark your reply above as answer? It will benefit all community members who are facing similar issues when they read this thread. Your contribution is highly appreciated. 

    Thanks for your kind understanding.

    Best Regards,

    Herb 


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

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, November 20, 2019 1:41 AM
  • Just as observations...

    ...As a general rule, Word (a) calculates field results starting at the beginning of the selection and proceeding to the end, and uses the values of bookmarks as "most recently calculated".

    So until Word gets to the end of the selection and has calculated all the bookmark values in the selection, it may be using "old" bookmark values when doing earlier calculations.

    That means that however you make a "forward reference," i.e. a reference to a result that is created further down the document, the value of that forward reference may not be correct until you have updated all the fields twice.

    I don't actually think there is a simple way around that - the simplest way is probably not to make forward references, as you have tried. Trying to persuade your user(s) to update the values twice is another way.

    You can see a crude example of this if you enter the following fields 

    { =bookmark1 }
    { SET bookmark1 1 }

    and avoid updating the SET field.

    The first time you ctrl-A, F9, you will see something like

    !Undefined Bookmark, BOOKMARK1

    because the bookmark hasn't been created. Next time you should see 

    1

    ...I don't think the DEFINED function will help you here, as you have discovered, but actually I would avoid using DEFINED() anyway for a number of reasons.

    Reason 1 is that you cannot pass any old parameter to DEFINED() and have it return 1 or 0. It can also return various kinds of field error. A simple example is if you try

    { =DEFINED(1/0) }

    Reason 2 is that the behaviour of DEFINED when you reference a bookmark in its parameter varies depending on exactly how you defined the bookmark.

    In your situation, in effect you had this...

    { =DEFINED(TotalProvSum) }

    And further down the document in a table you had something like

    { =SUM(ABOVE) }

    and you had applied the bookmark TotalProvSum to that field or even the entire cell it is in (I think)

    In *that* case, when you select the text from the DEFINED field to the SUM field and press F9, what Word seems to do is start by undefining TotalProvSum. So { =DEFINED(TotalProvSum) } returns 0. The same thing happens when you use ctrl-A, F9.

    However if in that table you did this:

    { SET TotalProvSum { =SUM(ABOVE) } } { TotalProvSum }

    and selected the same material and updated the fields (or ctrl-A, F9),

    { =DEFINED(TotlProvSum) } returns 1.

    Finally, if you just have a piece of text containing a value such as 123.45 and bookmark it with TotalProvSum, and select the fields/bookmarked text in a similar way, { =DEFINED(TotalProvSum) } returns 1.

    There's more, but IMO that's already enough to suggest that this isn't a function anyone should rely on unless they are very sure they have tested it adequately for their use case.



    Peter Jamieson


    • Marked as answer by gbkiwi Thursday, November 21, 2019 8:44 AM
    • Edited by Peter Jamieson Thursday, November 21, 2019 12:02 PM
    Wednesday, November 20, 2019 8:19 PM
  • Using Word 2019 for Windows 1910 (Build 12130.20344 Click-to-Run), I can't duplicate the problem. The final total updates as expected with Ctrl + A and F9.

    However, I've seen it many times in using formulae in Word, that it doesn't always like to chain calculations (first add the subtotals, then add the subtotals together). In such a case, you can work around it by bookmarking the columns of numbers and doing one addition of all of them in the grand total formula.

    Thursday, November 21, 2019 3:00 AM