Conditional IF statement in a Word table?

Question

I have a formatted, field-coded Word document that's generated by a database. It has a table with a set size (say it's 10 rows) that has a column that contains individual $ values, and another column that subtracts one from the other. Something like this:

	Starting balance	$83
Transaction 1	$20	$63
Transaction 2	$20	$43
Transaction 3	$50	($7)

So C2 has a simple formula, =C1-B2. and so on.

The problem is we don't know how many rows there are going to be... there could be one, there could be seven. If there are too few rows, I end up with something like this:

	Starting balance	$83
Transaction 1	$20	$63
		$63
		$63

I would like C3 to have an IF statement that says "If B2 is blank, do nothing, If B2 has a number then C2-B2." The closest I got was a statement that puts a $0 in C3, but I want it to be blank.

Is there an IF statement I can put in a Word table that uses a formula for one result, and returns a blank otherwise?

Answer 1

If you send me a copy of the document, referencing this thread in the covering email message, I will investigate the issue.

Answer 2

Thank you so much for the reply. I am not able to get this or the other solution to work. A blank is always returned, regardless of if there's something in column B. It seems like it should work. It's not returning a syntax error.

This is the actual code I'm using:

{ IF{ = D4 - C5 } = { = D4 } "" { = D4 - C5 } }

[different sized table than my example, but same idea]

Even if there's something in C5, it returns a blank when I 'Update Field'. I changed it to a plain

{ = D4 - C5 }

just to check, and it returns the expected number.

Am I missing something?

Answer 3

You might consider using the Many to One facility on my Merge Tools Add-in tthat 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.
• 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.

Answer 4

Use a field code as shown in C3

Answer 5

The field construction you need looks like this:

which updates to

This is much easier to do in Excel than in Word. In the table above, each field has to be copied from the one above and then manually adjusted to refer to the correct row numbers. In Excel, using the Fill Down command automatically adjusts each cell's formula. Then you can copy the whole thing from Excel and paste it into Word, where it will continue to operate as an Excel object.