Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Location
    Sydney, New South Wales
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I hope someone can help me with the following problem.

    I have a table that has a couple of subtotals. I have split the table and subtotals into different tables for each section and applied bookmarks to all sections. I have applied a bookmark called Table1 to the first table, Table2 etc. I have called the subtotal rows st1, st2 etc (I have not used these as yet in my calculation) - See attached document.

    I have managed to get the formulas for each section to work but I cannot find any information on what to add in this formula if a section is deleted. Eg if the table inside the Table2 bookmark is deleted what would add to the formula so that the bookmark will be ignored and not return the error !Undefined bookmark, TABLE 2.

    Inside the field I have the following:
    { =SUM(Table1 B:B)-SUM(Table2 B:B)-SUM(Table3 B:B)-SUM(Table4 B:B) \# "$#,##0.00" }

    I found a very good document on this site on Word Field Maths which helped me up to this point but countless searches on google does not bring any results for this type of query.

    I have no knowledge of vba so I cannot use vba to fix this problem.

    Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    You can test for the error using an IF field. I'm not much of a field whiz, but this works for me.

    • Press Alt+F9 to toggle the display to field codes (instead of field results)
    • Select you existing field and press Ctrl+F9 to surround it with a new field
    • Just inside the new opening field brace, type IF and then press Ctrl+F9 again
    • Inside the new new opening field brace, type REF "Tablex" (where Tablex is the bookmarks you want to test for)
    • Just after the corresponding closing field brace, paste the following ="Error! Reference source not found."
    • Type a space and then type "-" (you can change this later, but this will help with debugging)
    • Make sure there is a space before the opening field brace for the "real" result
    • Press Alt+F9 to toggle the display to field results

    Now you can test by adding or removing the bookmark and refreshing the IF field.

    It's a lot of edits. For the second and subsequent, you can shortcut the process by creating the outer field code and pasting from the IF through the "-" and then editing the bookmark.

    Any luck? (I'm sure someone will have a more efficient suggestion...)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd use Excel for this - it is much more suitable for formulas.


  4. #4
    New Lounger
    Join Date
    Oct 2003
    Location
    Sydney, New South Wales
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Jscher. I tried your method and all I got was a dash. Not sure if I did this right but I want users to be able to delete rows if need be.

    I cannot use Excel for this as the client copies this table out of the letter sent into another document and specifies word format.

    I will just have to tell all the users of this document not to delete any rows.

    Thank you for your responses.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by lcoleman View Post
    Thanks Jscher. I tried your method and all I got was a dash. Not sure if I did this right but I want users to be able to delete rows if need be.
    The dash shows that the formula recognized a missing bookmark. Change the dash to whatever you like: it can be blank or it can be a note to the user, such as, "You can delete this row now." However, as far as I know, there's no way to wrap the entire table row in a field, so you can't completely suppress it if the user removes a bookmark.

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Location
    Sydney, New South Wales
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That makes sense now. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •