mail merge - text wont display correctly

[rascl]

Hello,
I am using office 2003, Word/Excel.
I am having trouble getting my text to display correctly in my merge
doc. The text displays as 0 but only values from 2 or 3 different
columns. The other text values (other columns) display correctly. I am
very confused. I have played with the formatting in excel even though I
understanding that word ignores that formatting (?) Previously I found
that the “confirm conversion at open” solved my problem up until it
stopped working. That is to say, word will no longer stay connected (?)
to my data source even though it does open my spreadsheet, it tells me
it cannot find it & gives me the option of going to get it. If I choose
to go get it from there I will continue to get the same results (cannot
find the data source). Instead, upon opening my word doc., I have to
choose “no” do not connect to my data source and start from the
beginning each time.
I hope that was clear enough .. I would greatly appreciate any
suggestions!

 

[Cindy M.]

Hi Rascl,

I am using office 2003, Word/Excel.
I am having trouble getting my text to display correctly in my merge
doc. The text displays as 0 but only values from 2 or 3 different
columns. The other text values (other columns) display correctly. I am
very confused. I have played with the formatting in excel even though I
understanding that word ignores that formatting (?) Previously I found
that the “confirm conversion at open” solved my problem up until it
stopped working. That is to say, word will no longer stay connected (?)
to my data source even though it does open my spreadsheet, it tells me
it cannot find it & gives me the option of going to get it. If I choose
to go get it from there I will continue to get the same results (cannot
find the data source). Instead, upon opening my word doc., I have to
choose “no” do not connect to my data source and start from the
beginning each time.
I hope that was clear enough ..

I'm not sure I follow... "The text displays as 0 but only values from 2
or 3 different columns" can be interpreted in a couple of ways. You mean
some columns pass a "0" instead of the content in the cell? Or do you
mean values from one column are appearing in the field for a different
column?

If you mean the first: do these columns have mixed text and numeric
entries? And the first few entries are numeric?

If that's the case, select one of the columns. Go to the DATA menu and
choose "Text to columns". The most important step is where you select the
option that sets the data type. By default it's "General"; you want to
specifically say it's "Text". This will allow mixed text and numbers, no
matter how you connect.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[rascl]

Hi Rascl,

I'm not sure I follow... "The text displays as 0 but only values fro
2
or 3 different columns" can be interpreted in a couple of ways. Yo
mean
some columns pass a "0" instead of the content in the cell? Or do you
mean values from one column are appearing in the field for a different
column?

If you mean the first: do these columns have mixed text and numeric
entries? And the first few entries are numeric?

If that's the case, select one of the columns. Go to the DATA menu and
choose "Text to columns". The most important step is where you selec
the
option that sets the data type. By default it's "General"; you want to
specifically say it's "Text". This will allow mixed text and numbers
no
matter how you connect.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow questio
or
reply in the newsgroup and not by e-mail

Ah! Cindy that’s awesome .. Except ): one of my columns contain
formulas to pull in data. It turns my formula into text so that i
doesn’t work – anyway around that one??
I appreciate your help!!! I have posted this before (other forum) bu
you are the only one to reply. Thank yo

 

[Cindy M.]

Hi Rascl,

Ah! Cindy that’s awesome .. Except ): one of my columns contains
formulas to pull in data. It turns my formula into text so that it
doesn’t work – anyway around that one??
I appreciate your help!!! I have posted this before (other forum) but
you are the only one to reply.

That's why we have some forums that are topic-specific Mail merge is a
very specialized area...

You have two choices

1. Copy the problem column VALUES to another column.

2. Use the TEXT function ("around" the current formula) to explicitly
format the result as text. In my experience, Word respects that.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[rascl]

I’m afraid I don’t get what you’re saying. I don’t know near as much as
I would like to and I’m sure not a fraction of what you do .. this is
kicking my butt!! ):
Apparently my formulas do not recognize the values from the columns
using the text function either.
A little more info:
I have 39 columns of information (1800 rows so far) either entered or
pulled in by formulas that could be any combination of text and
numbers. I use as many formulas as I possibly can to pull in info from
other spreadsheets to save time. -I do copy/paste values over all rows
completed so I only have a minimal number of rows with formulas. I
pretty much depend on mail merge to bring everything together. Reports,
credits, packing slips, shipping docs, pull logs, labels. ugh!
I am the only one (so far) upgraded to Office 2003 .. it looks like I
may have to go back to 2000 if I cant figure this out. Our IT dept. has
determined it is not a network issue (they did all updates & suggestions
Microsoft offered). I mention that because my spreadsheet was on the
network, they moved some stuff around & the DDE thing quit working
(coincidence?) so I saved it to my desktop – worked fine for awhile,
then quit again. I have gotten at least on engine failure message when
trying to connect to my data source .. “This operation cannot be
completed because of dialog or database engine failures” .. blablabla.

Too much info? Sorry. Don’t want to waste your time, perhaps you could
suggest mail merge reading material? – I obviously need to study but am
definitely willing to try any suggestions – if you could use little
words |:
Thanks again!

That's why we have some forums that are topic-specific Mail merge
is a
very specialized area...

You have two choices

1. Copy the problem column VALUES to another column.

2. Use the TEXT function ("around" the current formula) to explicitly
format the result as text. In my experience, Word respects that.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or
reply in the newsgroup and not by e-mail

 

[Cindy M.]

Hi Rascl,

“This operation cannot be
completed because of dialog or database engine failures”

This often indicates that the MDAC (Microsoft Data Access) on the
machine needs to be updated. You IT folks should understand what that
means

Apparently my formulas do not recognize the values from the columns
using the text function either.

2. Use the TEXT function ("around" the current formula) to explicitly
format the result as text. In my experience, Word respects that.

In an earlier message you said you had one column where you couldn't use
"Text to columns" because the column contains a formula. Could you
please copy that formula from Excel's formula bar and paste it into your
reply?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[rascl]

Hi Cindy,
Thank you for your continued support! I have copied the formula below
however yesterday our IT dept. (different person) determined that not
only was something wrong with my network connection but apparently
some?thing wrong with my pc. They gave me a new pc & plugged me in to a
different network line. The problem(s) hasn’t re-occurred as yet. My
values are again displaying correctly without doing the confirm
conversion at open however that’s where the original problem started
and why I went to the confirm conversion in the first place… I’m not
entirely convinced that the problem wont re-occur but I’m hoping

At any rate I am still interested in the text to column matter if you
wouldn’t mind replying even though its no longer as urgent a need for
me (so far).
Here is my formula:
=IF(ISBLANK(VLOOKUP(A1770,'C:\Product Support\Warranty\2006 Warranty
Reports\Claims\[2006 Warranty Claims .xls]Warranty
Claims'!$A:$N,14,FALSE))," ",(VLOOKUP(A1770,'C:\Product
Support\Warranty\2006 Warranty Reports\Claims\[2006 Warranty Claims
xls]Warranty Claims'!$A:$N,14,FALSE)))

Thanks for all your help!!
rascl

 

[Cindy M.]

Hi Rascl,

They gave me a new pc & plugged me in to a
different network line. The problem(s) hasn’t re-occurred as yet. My
values are again displaying correctly without doing the confirm
conversion at open however that’s where the original problem started
and why I went to the confirm conversion in the first place… I’m not
entirely convinced that the problem wont re-occur but I’m hoping

OK, DDE is back in business - enjoy it while it lasts

At any rate I am still interested in the text to column matte

Let's see what we can work out...

Here is my formula:
=IF(ISBLANK(VLOOKUP(A1770,'C:\Product Support\Warranty\2006 Warranty
Reports\Claims\[2006 Warranty Claims .xls]Warranty
Claims'!$A:$N,14,FALSE))," ",(VLOOKUP(A1770,'C:\Product
Support\Warranty\2006 Warranty Reports\Claims\[2006 Warranty Claims
.xls]Warranty Claims'!$A:$N,14,FALSE)))

That's a "mouthful"

This is what I'd try. Note that I'm giving it a 0.00 numbering style
(at the end, before the closing parenthesis), but you can substitute
whatever you like. The important thing is that there's something:

=TEXT(IF(ISBLANK(VLOOKUP(A1770,'C:\Product Support\Warranty\2006
Warranty
Reports\Claims\[2006 Warranty Claims .xls]Warranty
Claims'!$A:$N,14,FALSE))," ",(VLOOKUP(A1770,'C:\Product
Support\Warranty\2006 Warranty Reports\Claims\[2006 Warranty Claims
xls]Warranty Claims'!$A:$N,14,FALSE))), "0.00")

Note: If I'm reading this correctly, the problem may be with the
"True" section. You're setting this to a space? Then a number format
may not be accepted, or may give you a result you don't want. In that
case, try something more along these lines:

=IF(ISBLANK(VLOOKUP(A1770,'C:\Product Support\Warranty\2006 Warranty
Reports\Claims\[2006 Warranty Claims .xls]Warranty
Claims'!$A:$N,14,FALSE))," ",Text((VLOOKUP(A1770,'C:\Product
Support\Warranty\2006 Warranty Reports\Claims\[2006 Warranty Claims
xls]Warranty Claims'!$A:$N,14,FALSE)), "0.00"))

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[rascl]

Thank you! Thank you! I will try that asap!!
-currently very behind due to all the problems I was having.

Note: If I'm reading this correctly, the problem may be with the
"True" section. You're setting this to a space?

The only reason I set "true" to a space is so that I will get n
display if there is no value available, instead of 0 or n/a showing u
in my form letter. There is probably a better way (?) and maybe not s
harry of a formula but I don’t know about one yet... I'm pretty muc
self taught .. rather, I started out swiping formulas and figuring the
out so I could use them|: I have minimal classes taken now but none s
far that focus on writing formulas ..
Thank you a million times !! I look forward to trying your suggestio
asap!