Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
I am trying to import data from Excel into a Word document via mail merge.
All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks -- qwerty |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
What does the field produce without a switch for each condition?
The Yes, No and possibly the null should reproduce what you have in the field. The only one that seems likely to cause trouble is the percentage field. That being the case, on the face of it you need a nest of conditional fields that test each possible result ie { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words beginning upper case Y i.e. YES or Yes) { IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words beginning upper case N i.e. NO or No) { IF{ MERGEFIELD Target } = "" "" } (Look for Null) { IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" } so that would give you: { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" "" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" } Each pair of field brackets is inserted with CTRL+F9. Switches are covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org John wrote: I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
Hi John,
You could probably do this with a compund field coded as: {IF{MERGEFIELD Target}= "" "N/A" {IF{={MERGEFIELD Target}}= {MERGEFIELD Target} {={MERGEFIELD Target}*100 \# ",0%"} {MERGEFIELD Target}}} Alternatively, by using 5 copies of your «Target» mergefield, you could use: {IF«Target»= "" "N/A" {IF{=«Target»}= «Target» {=«Target»*100 \# ",0%"} «Target»}} In both cases, be careful to preserve the indicated spacing. Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "John" wrote in message news I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks -- qwerty |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
Graham - Thanks for the response. Without a switch, the field returns a
non-rounded number when the source is populated with a number and 'N/A' or null when the field is populated with 'N/A' or null respectively. I will read through your website below and try your suggested switch then let you know how it came out. Thanks again. qwerty "Graham Mayor" wrote: What does the field produce without a switch for each condition? The Yes, No and possibly the null should reproduce what you have in the field. The only one that seems likely to cause trouble is the percentage field. That being the case, on the face of it you need a nest of conditional fields that test each possible result ie { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words beginning upper case Y i.e. YES or Yes) { IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words beginning upper case N i.e. NO or No) { IF{ MERGEFIELD Target } = "" "" } (Look for Null) { IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" } so that would give you: { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" "" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" } Each pair of field brackets is inserted with CTRL+F9. Switches are covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org John wrote: I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
Revising my response from earlier today:
When I use the merge field without a switch it works fine so long as there is a number as the source data. However, It returns "0" (zero) when the source field states "Yes". This was an issue before I responded earlier but I thought I had fixed it by closing and re-opening the two files. While trying to implement your and macropod's solutions, I found that it would still return "0" when the source was "Yes". I went back to the simple merge field only to learn it is returning 0 even after closing and re-opening the files. The "No" and Null scenarios are not possible for my current field. Simplifying the solution for "Yes" or a percentage format may give me the starting point for the next field which does include "No" and nulls. "Graham Mayor" wrote: What does the field produce without a switch for each condition? The Yes, No and possibly the null should reproduce what you have in the field. The only one that seems likely to cause trouble is the percentage field. That being the case, on the face of it you need a nest of conditional fields that test each possible result ie { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words beginning upper case Y i.e. YES or Yes) { IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words beginning upper case N i.e. NO or No) { IF{ MERGEFIELD Target } = "" "" } (Look for Null) { IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" } so that would give you: { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" "" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" } Each pair of field brackets is inserted with CTRL+F9. Switches are covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org John wrote: I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
Hi John,
Check you mailmerge data connection type, and make sure it's DDE. -- Cheers macropod [MVP - Microsoft Word] "John" wrote in message ... Revising my response from earlier today: When I use the merge field without a switch it works fine so long as there is a number as the source data. However, It returns "0" (zero) when the source field states "Yes". This was an issue before I responded earlier but I thought I had fixed it by closing and re-opening the two files. While trying to implement your and macropod's solutions, I found that it would still return "0" when the source was "Yes". I went back to the simple merge field only to learn it is returning 0 even after closing and re-opening the files. The "No" and Null scenarios are not possible for my current field. Simplifying the solution for "Yes" or a percentage format may give me the starting point for the next field which does include "No" and nulls. "Graham Mayor" wrote: What does the field produce without a switch for each condition? The Yes, No and possibly the null should reproduce what you have in the field. The only one that seems likely to cause trouble is the percentage field. That being the case, on the face of it you need a nest of conditional fields that test each possible result ie { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words beginning upper case Y i.e. YES or Yes) { IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words beginning upper case N i.e. NO or No) { IF{ MERGEFIELD Target } = "" "" } (Look for Null) { IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" } so that would give you: { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" "" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" } Each pair of field brackets is inserted with CTRL+F9. Switches are covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org John wrote: I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
From the Tools menu in Word, select Options and then go to the General tab
and check the box against the "Confirm conversions at open" item. (In Word 2007 it is Office Button Word Options Advanced General Confirm file format conversion on open). Then when you attach the data source to the mail merge main document, you will be given the option of using the DDE method of connection which should read the data as you have it formatted in the table. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi John, Check you mailmerge data connection type, and make sure it's DDE. "John" wrote in message ... Revising my response from earlier today: When I use the merge field without a switch it works fine so long as there is a number as the source data. However, It returns "0" (zero) when the source field states "Yes". This was an issue before I responded earlier but I thought I had fixed it by closing and re-opening the two files. While trying to implement your and macropod's solutions, I found that it would still return "0" when the source was "Yes". I went back to the simple merge field only to learn it is returning 0 even after closing and re-opening the files. The "No" and Null scenarios are not possible for my current field. Simplifying the solution for "Yes" or a percentage format may give me the starting point for the next field which does include "No" and nulls. "Graham Mayor" wrote: What does the field produce without a switch for each condition? The Yes, No and possibly the null should reproduce what you have in the field. The only one that seems likely to cause trouble is the percentage field. That being the case, on the face of it you need a nest of conditional fields that test each possible result ie { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words beginning upper case Y i.e. YES or Yes) { IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words beginning upper case N i.e. NO or No) { IF{ MERGEFIELD Target } = "" "" } (Look for Null) { IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" } so that would give you: { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" "" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" } Each pair of field brackets is inserted with CTRL+F9. Switches are covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org John wrote: I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
There's an explanation of why this occurs at
http://tips.pjmsn.me.uk/t0003.htm -- Peter Jamieson http://tips.pjmsn.me.uk "John" wrote in message ... Revising my response from earlier today: When I use the merge field without a switch it works fine so long as there is a number as the source data. However, It returns "0" (zero) when the source field states "Yes". This was an issue before I responded earlier but I thought I had fixed it by closing and re-opening the two files. While trying to implement your and macropod's solutions, I found that it would still return "0" when the source was "Yes". I went back to the simple merge field only to learn it is returning 0 even after closing and re-opening the files. The "No" and Null scenarios are not possible for my current field. Simplifying the solution for "Yes" or a percentage format may give me the starting point for the next field which does include "No" and nulls. "Graham Mayor" wrote: What does the field produce without a switch for each condition? The Yes, No and possibly the null should reproduce what you have in the field. The only one that seems likely to cause trouble is the percentage field. That being the case, on the face of it you need a nest of conditional fields that test each possible result ie { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words beginning upper case Y i.e. YES or Yes) { IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words beginning upper case N i.e. NO or No) { IF{ MERGEFIELD Target } = "" "" } (Look for Null) { IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" } so that would give you: { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" "" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" } Each pair of field brackets is inserted with CTRL+F9. Switches are covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org John wrote: I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
"John" wrote: Revising my response from earlier today: When I use the merge field without a switch it works fine so long as there is a number as the source data. However, It returns "0" (zero) when the source field states "Yes". This was an issue before I responded earlier but I thought I had fixed it by closing and re-opening the two files. While trying to implement your and macropod's solutions, I found that it would still return "0" when the source was "Yes". I went back to the simple merge field only to learn it is returning 0 even after closing and re-opening the files. The "No" and Null scenarios are not possible for my current field. Simplifying the solution for "Yes" or a percentage format may give me the starting point for the next field which does include "No" and nulls. "Graham Mayor" wrote: What does the field produce without a switch for each condition? The Yes, No and possibly the null should reproduce what you have in the field. The only one that seems likely to cause trouble is the percentage field. That being the case, on the face of it you need a nest of conditional fields that test each possible result ie { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words beginning upper case Y i.e. YES or Yes) { IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words beginning upper case N i.e. NO or No) { IF{ MERGEFIELD Target } = "" "" } (Look for Null) { IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" } so that would give you: { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" "" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" } Each pair of field brackets is inserted with CTRL+F9. Switches are covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org John wrote: I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks Thanks Graham - This worked. |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
If "Yes" then "Yes or round as percentage
Thanks Graham - This worked.
"Graham Mayor" wrote: From the Tools menu in Word, select Options and then go to the General tab and check the box against the "Confirm conversions at open" item. (In Word 2007 it is Office Button Word Options Advanced General Confirm file format conversion on open). Then when you attach the data source to the mail merge main document, you will be given the option of using the DDE method of connection which should read the data as you have it formatted in the table. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi John, Check you mailmerge data connection type, and make sure it's DDE. "John" wrote in message ... Revising my response from earlier today: When I use the merge field without a switch it works fine so long as there is a number as the source data. However, It returns "0" (zero) when the source field states "Yes". This was an issue before I responded earlier but I thought I had fixed it by closing and re-opening the two files. While trying to implement your and macropod's solutions, I found that it would still return "0" when the source was "Yes". I went back to the simple merge field only to learn it is returning 0 even after closing and re-opening the files. The "No" and Null scenarios are not possible for my current field. Simplifying the solution for "Yes" or a percentage format may give me the starting point for the next field which does include "No" and nulls. "Graham Mayor" wrote: What does the field produce without a switch for each condition? The Yes, No and possibly the null should reproduce what you have in the field. The only one that seems likely to cause trouble is the percentage field. That being the case, on the face of it you need a nest of conditional fields that test each possible result ie { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words beginning upper case Y i.e. YES or Yes) { IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words beginning upper case N i.e. NO or No) { IF{ MERGEFIELD Target } = "" "" } (Look for Null) { IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" } so that would give you: { IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" "" "{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" } Each pair of field brackets is inserted with CTRL+F9. Switches are covered at http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org John wrote: I am trying to import data from Excel into a Word document via mail merge. All data in Excel is derived via a formula. One field Target is giving me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I have tried If Then and nothing seems to work to get the results I need. If the value is Yes, then I need it to state Yes, If it is a percentage, I need it to format as a percent and round to the 1st decimal. If it is null, I need it to state "N/A" Incidentally, I do not understand the merge switch concept and why it seems to toggle between three states, then two. Resources for this would be appreciated as well. Thanks |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
email merge with multiple recipients in "To", "CC" & "BCC" | Mailmerge | |||
I can't "Open" a downloaded template. Only "Save" and "Cancel" | Microsoft Word Help | |||
What does "char" "char1" "char2" mean in styles in Word? | Microsoft Word Help | |||
Word: 'track changes": have "changed by" rather "del." and "inser | Microsoft Word Help | |||
The "Symbol" under "Insert" disappeared and replaced by "Number" | Microsoft Word Help |