TRANSPOSE results in zero values for blank cells

[ramatsu]

I have a worksheet of contact data that is in columns for convenience in
editing, but needs to be in rows in order to be used by Word as a mail merge
data source.

I used TRANSPOSE (the function, not the Paste Special option) to populate a
worksheet with transposed values, but all the blank cells from the original
(where a contact does not have, for example, a fax number, etc.) are
populated with zeros. I've turned off the display of these zeros in the Error
Checking prefs, so it looks fine, but...

When I use the transposed sheet as a data source for Word mail merge, all
these zeros come in. So instead of ignoring empty cells and not inserting
an address or phone line when there's no data, Word merges in a line of text
with the number zero (0) in it.

This doesn't occur if I use the Paste Special "Transpose" option, so it has
something to do with the way the TRANSPOSE function is interpreting those
empty cells and populating them.

Is there anything I can add to the TRANSPOSE function applied to the cells
in the target worksheet to get rid of these zero values? Here's what the
function looks like right now:

=TRANSPOSE(DataSourceWorkbookname.xlsx!NamedDataRange)

I am using Mac Office 2008, so at least until 2011 or 2012, there's no VB
options for me at the Word end, alas.

Thanks!

 

[Max]

Try it with a simple error trap to return "blanks", ie ""
eg, array-entered:
=IF(TRANSPOSE(...)=0,"",TRANSPOSE(...))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[MyVeryOwnSelf]

I have a worksheet of contact data that is in columns for convenience

in editing, but needs to be in rows in order to be used by Word as a
mail merge data source.

I used TRANSPOSE (the function, not the Paste Special option) to
populate a worksheet with transposed values, but all the blank cells
from the original (where a contact does not have, for example, a fax
number, etc.) are populated with zeros. ...

When I use the transposed sheet as a data source for Word mail merge,
all these zeros come in. ...

Is there anything I can add to the TRANSPOSE function applied to the
cells in the target worksheet to get rid of these zero values?

The following doesn't use the TRANSPOSE function, but maybe it'll help.

If the original data is in Sheet1, put this in Sheet2!A1
=IF(OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)="","",
OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1))
Then extend the formula to the right and down as far as needed.

I have Office 2003 for Windows.

 

[ramatsu]

Thanks! That did the trick - Not sure why the function returns zeros in the
first place, but I'm glad that this trap intercedes effectively.

 

[Max]

Glad to hear, you're welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik