Forum Discussion

charliebobgo's avatar
charliebobgo
Copper Contributor
Apr 24, 2024

Word table formula with SEQ operators not working properly on one computer but fine on others

I'm trying to resolve an issue for a customer with a document that uses SEQ formula operators.

I've created an invoice document template and the table uses some SEQ formula operators to calculate the invoice total, amongst some other things, but the template doesn't work correctly on my clients machine. When the fields are updated using F9, the dollar symbols are repeated and decimal places are introduced even when these are zero.

 

To be clear, the template works correctly on my computer. It also works correctly on my client's colleagues computer too.

 

I'm confused as to what the problem is. We're both using Windows 11 and we're both using the latest version of Microsoft Office 365 but there must be something different between the two installations? Here's some things I've already tried to resolve the problem:

 

  • Client was previously on 'Microsoft Office Mondo 2016' which was thought to be the source of the problem but we've now installed a trial of the latest Microsoft Office 365. Before they updated from Mondo, we were getting messages about the contents of the file being unreadable, but these now don't appear.
  • Verified that 'Protected View' or anything to do with the Trust Centre is blocking the formulas.
  • Disabled any Add-Ins.
  • Ran the Word Document in safe mode.
  • Stripped all formatting from the document to rule out other conflicts
  • The document was sent to the client via email, I tried sending via WeTransfer instead, incase email was corrupting the file.

Does anyone have any suggestions?

 

Here's what formula code I'm using in one of the cells. I'm not an expert in these SEQ operators but I built this based off some other formulas I found on StackExchange, basically it multiplies three cells together to create a total for the row, it works relatively so the same code can be used in all total cells and it won't break if other rows above or below are deleted.

${ =({ QUOTE "D{ =1+{ SEQ row \* Arabic } \* Arabic }" }*{ QUOTE “E{ =1+{ SEQ row \c \* Arabic } \* Arabic }" }*{ QUOTE “F{ =1+{ SEQ row \c \* Arabic}\* Arabic }" }) }

Here's a video of what happens, we're using a stripped back version of the template in that example. I've also uploaded an anonymised version of the file to WeTransfer if anyone wants to test the file directly.

 
  • charliebobgo For a simpler method of producing documents such as invoices, use the Many to One facility on my Merge Tools Add-in that is contained in the MERGE TOOLS ADD-IN.zip file that you can download from:

    https://mergetoolsaddin.com/

    Extract the files from the archive and read the:

    “READ ME – Setting up and using the Merge Tools Add-in.pdf

    to see how to install and use the various tools.

    Using those tools, it is possible to perform the following types of merge that cannot be done with Mail Merge “out-of-the-box”:

    • Merge to e-mail messages either with or without attachments, with the documents created by the merge being sent as either Word or PDF attachments or as the body of the e-mail message .  The email messages can, if necessary, also be sent to CC and BCC addresses and the subject of the message can include data from a field in the data source.
    • Merge to individual documents in either Word or PDF format with the filenames being supplied by the data in one of the fields in the data source
    • Many to One type merges, which can be used for creating documents such as invoices where there are multiple records in the data source that have common data in one of the fields
    • Merging to a document that will include a chart that is unique to each record in the data source
    • Merging a document with Content Controls
    • Merging a document that contains Legacy FormFields
    • Duplex Merges
    • Merging to a printer that will collate and staple the output created from each record in the data source.

    The requirements for using the system are:

    • The mail merge main document must be of the Letters type, though that does not mean that the output cannot be sent as an e-mail message where relevant.
    • For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet. For the Chart Merge utility, see the Mail Merging with Charts document that is included in the Merge Tools Add-in Zip file for additional requirements for the data source for use with that utility
    • For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.
    • For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.

    The MergeTools Add-in will NOT work with the “New Outlook”, which is just a re-badged version of Windows Mail and like its predecessor, it does not support automation and hence will not send the messages created by the MergeTools Add-in.

    Be aware, that any messages that you had tried to send with the “New Outlook” will have been placed in the Outbox of the original Outlook and they will be sent as soon as you revert to that version of Outlook.   If you do not want that to happen, you should put Outlook Off-line, or disconnect your computer from the Internet.

Resources