Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRANSPOSE results in zero values for blank cells
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!NamedDataRa nge) 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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRANSPOSE results in zero values for blank cells
Hi,
there is no need to post 3 times, it just means some of us answer one before we discover the other. And our answers may be the same as you already have, a waste of our time. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ramatsu" wrote: 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!NamedDataRa nge) 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TRANSPOSE results in zero values for blank cells
Yes, my apologies - this happened because when I hit "Post," the confirmation
window just showed an endlessly spinning graphic, and I didn't think the post went through. I re-submitted because I thought it had failed the first 2 times. (I wouldn't intentionally post multiple times.) I haven't used the webform for this group in the past (usually use Entourage but I can't recall how to see what return email address Entourage will expose to spambots) so the Cross-Post field was new to me. I did use that to cross-post between the excel.worksheet.functions group and the excel.mac group since there's presumably less shared audience between them. Is use of the cross-post field for this kind of thing discouraged too? I really appreciate the efforts by folks who provide help here, so want to be a good citizen. I'm trying your tip, I'll click "Helpful" for any and all (you and Max) who offered it if it works. Looks promising. Thanks, Allen "Shane Devenshire" wrote: Hi, there is no need to post 3 times, it just means some of us answer one before we discover the other. And our answers may be the same as you already have, a waste of our time. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ramatsu" wrote: 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!NamedDataRa nge) 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRANSPOSE results in zero values for blank cells | Excel Worksheet Functions | |||
Counting cells containing formulas with blank results | Excel Discussion (Misc queries) | |||
Transpose from one sheet to the next and leave out blank cells | Excel Discussion (Misc queries) | |||
Results from blank linked cells | New Users to Excel | |||
hiding blank cells when no results | Excel Worksheet Functions |