Mail Merge bringing over blank fields

Hi,

I currently have a mail merge set up to read data from an excel file and
populate mergefields in my word file. Several of the fields in the
spreadsheet use an IF statement in a formula to test if the row is in use and
either calculates a value or leave the cell blank if the row is not populated
with any other data.

When run the merge brings across the actual data from the spreadsheet but
also brings across what appear to be blank fields or values calculated as
zero from the sheet.

I assumed if the merge round no data in the next row it would stop the merge
but this is not happening.

Examples of my IF statements are;
- =IF(A4="","",(TEXT(TODAY()+2,"dddd dd mmmm yyyy")))
- =IF(A4="","",(E4+F4))

Any ideas gratefully received, thanks.

The presence of the formulae will result in the mail merge treating those
rows as records.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"Squeekykilt" wrote in message
...
Hi,

I currently have a mail merge set up to read data from an excel file and
populate mergefields in my word file. Several of the fields in the
spreadsheet use an IF statement in a formula to test if the row is in use
and
either calculates a value or leave the cell blank if the row is not
populated
with any other data.

When run the merge brings across the actual data from the spreadsheet but
also brings across what appear to be blank fields or values calculated as
zero from the sheet.

I assumed if the merge round no data in the next row it would stop the
merge
but this is not happening.

Examples of my IF statements are;
- =IF(A4="","",(TEXT(TODAY()+2,"dddd dd mmmm yyyy")))
- =IF(A4="","",(E4+F4))

Any ideas gratefully received, thanks.

If the issue is to do with determining the last row of the data then it
is probably simpler to
a. create a range name that covers the area you need
b. manually or programmatically adjust that area as required
c. use that range as the data source for your merge

There may even be a way to set a range that automatically adjusts to
cover the area you need (i.e. just using worksheet formulas rather than
VBA), but if so, I certainly don't know how to do that.

Peter Jamieson

http://tips.pjmsn.me.uk

On 08/03/2010 22:41, Squeekykilt wrote:
Hi,

I currently have a mail merge set up to read data from an excel file and
populate mergefields in my word file. Several of the fields in the
spreadsheet use an IF statement in a formula to test if the row is in use and
either calculates a value or leave the cell blank if the row is not populated
with any other data.

When run the merge brings across the actual data from the spreadsheet but
also brings across what appear to be blank fields or values calculated as
zero from the sheet.

I assumed if the merge round no data in the next row it would stop the merge
but this is not happening.

Examples of my IF statements are;
- =IF(A4="","",(TEXT(TODAY()+2,"dddd dd mmmm yyyy")))
- =IF(A4="","",(E4+F4))

Any ideas gratefully received, thanks.

Thanks for the reply.

I had suspected that may be the case but the strange thing is that I have
several mail merges which work with the date formula
=IF(A4="","",(TEXT(TODAY()+2,"dddd dd mmmm yyyy"))) in place and they only
bring the rows with actual data.

The only apparent difference between the two is that the ones which work
only have that date calculation formula whilst my new merge uses a sheet with
several new formulae. Is it likely the merge is able to ignore the date
calculation but not the other formulae?

Thanks.

"Doug Robbins - Word MVP" wrote:

The presence of the formulae will result in the mail merge treating those
rows as records.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"Squeekykilt" wrote in message
...
Hi,

I currently have a mail merge set up to read data from an excel file and
populate mergefields in my word file. Several of the fields in the
spreadsheet use an IF statement in a formula to test if the row is in use
and
either calculates a value or leave the cell blank if the row is not
populated
with any other data.

When run the merge brings across the actual data from the spreadsheet but
also brings across what appear to be blank fields or values calculated as
zero from the sheet.

I assumed if the merge round no data in the next row it would stop the
merge
but this is not happening.

Examples of my IF statements are;
- =IF(A4="","",(TEXT(TODAY()+2,"dddd dd mmmm yyyy")))
- =IF(A4="","",(E4+F4))

Any ideas gratefully received, thanks.