Share this content
0
545

IF statements and Word

IF statements and Word

I'll keep it basic, but I have Bigger Plans.

I want a line in a letter to say "You have tax to pay" or "You are due a refund".  As we mail merge there are several numbers that are inserted into the letter (and many more on the actual Excel spreadsheet that just don't get pulled in) that would show which statement was true.  Let's say the Excel file has a column called "Tax due/repayable this year" and the Word document "(«Tax_due/repayable_this_year»)" as a field.

I have found Insert Field and the IF statement, which appears to be what I need.  But I can't find the correct words to use, specifically what I should put in this instance for Expression 1 Operation Expression 2 No matter what I put it never works).

I went for IF «Tax_due/repayable_this_year»>0 "Payment" "Repayment" but that displays ">0"

I tried IF Tax_due/repayable_this_year>0 "Payment" "Repayment" but that just says "Payment" regardless of the amount shown.

Any ideas?

Replies

Please login or register to join the discussion.

03rd Mar 2016 13:27

It will be something like...

this

{IF {MERGEFIELD Tax_due/repayable_this_year}<0 "Repayment" "Payment"}

You should be able to create this using the Mailing : Rules Wizard

Thanks (1)
03rd Mar 2016 14:31

Thanks

Paul D Utherone wrote:

this

{IF {MERGEFIELD Tax_due/repayable_this_year}<0 "Repayment" "Payment"}

You should be able to create this using the Mailing : Rules Wizard

I've tried almost every combination of the above, never get the results I need.

I did try and use the rules wizard as per some instructions I found online, but it is only useable once you have linked it to the data (as in, it seems I can't set up the document and then add data, I have to add data before I can do that stage).  Whether I should do the merge, then have some sort of macro to remove the parts I don't want...

Thanks (0)
03rd Mar 2016 14:46

You may be able to set a placeholder IF

Constantly Confused wrote:

Paul D Utherone wrote:

this

{IF {MERGEFIELD Tax_due/repayable_this_year}<0 "Repayment" "Payment"}

You should be able to create this using the Mailing : Rules Wizard

I've tried almost every combination of the above, never get the results I need.

I did try and use the rules wizard as per some instructions I found online, but it is only useable once you have linked it to the data (as in, it seems I can't set up the document and then add data, I have to add data before I can do that stage).  Whether I should do the merge, then have some sort of macro to remove the parts I don't want...

and then add the MERGEFIELD later when the data is attached, but it's easier to set up once the data is attached so that you can use the wizard.

You can set up the bulk of the letter and then attach data later and add the tweaks like name, address and numbers from/dependent on the data file, but I should have thought that trying to merge and the adjust the merged data possibly with a macro would be a long winded way of doing the merge and fraught with difficulties and the possibilities of error in the amendment than if you dealt with it all at the merge.

What version of Word are you using. I have had issues with merges and formatting in 2010 versions that go away if you save the data file as Excel 97-03 instead

Probably because I use Excel much more than Word I am far happier tinkering with macros in the former than the latter, which always seem very fiddly to write and easiest to do by recording and editing, but still a PITA

Thanks (1)
03rd Mar 2016 16:27

Progress?

Paul D Utherone wrote:

this

{IF {MERGEFIELD Tax_due/repayable_this_year}<0 "Repayment" "Payment"}

You should be able to create this using the Mailing : Rules Wizard

Ok, so I manually type that (complete with Ctrl + F9) instead of using the Word wizard, and yay it works... for the first record (which actually might be all I need, but...)

I then get Error! Unknown op code for conditional. for every other letter, including the first one if I try and go back to it.  Once I have merged the docs I notice the entry has become { IF 1.66 >=0 "Payment" "Repayment"} though...

 

Thanks (0)
03rd Mar 2016 14:17

I'd put it in the spreadsheet

But that's because I don't know how to do mailmerge.

I'd have a column with =IF(CELLREF>0, "You have tax to pay","You are due a refund") then stick the contents of that column in with no IF statement in Word. 

Thanks (1)
03rd Mar 2016 14:47

Hmmm

Duggimon wrote:

But that's because I don't know how to do mailmerge.

I'd have a column with =IF(CELLREF>0, "You have tax to pay","You are due a refund") then stick the contents of that column in with no IF statement in Word. 

That seemed like a perfect solution, but the data file is populated by another piece of software so sadly not viable.

Thanks (0)
03rd Mar 2016 16:06

Oh, and...

2010.

 

Thanks (0)
03rd Mar 2016 16:26

In which case

Constantly Confused wrote:

2010.

 

try saving your Excel data file as a 97-2003 file type rather than 2010 and see if that makes a difference...it's worked for me before :O
Thanks (1)
04th Mar 2016 08:57

Change the view options

with: File - Options - Advanced - and tick 'Show field codes instead of their values' (Alt + f)

Then edit the conditional to insert your field code. Where it is showing the number instead of the field at the moment is probably just down to the field code setting.

Then reset the view options to untick 'Show field codes...'

Then update the field

Getting optional items to work in Word merge files can be the most frustrating of jobs sometimes!

Thanks (0)
04th Mar 2016 09:26

Frustrated

Paul D Utherone wrote:

with: File - Options - Advanced - and tick 'Show field codes instead of their values' (Alt + f)

Then edit the conditional to insert your field code. Where it is showing the number instead of the field at the moment is probably just down to the field code setting.

Then reset the view options to untick 'Show field codes...'

Then update the field

Getting optional items to work in Word merge files can be the most frustrating of jobs sometimes!

Certainly the word I would go for.  I've spent many a happy hour/day/year sifting through macros/code to find the one line that is messing everything up, it is annoying that in this instance there is only one line, and somehow I still can't see what is wrong.

Roll on AI! 

"Word, I want to insert this text if the number above is positive, and this text if it is negative, make it so"

"Yes Dave, I will do that"

"Wait, who's Dave.......?"

#Ominous silence#

Thanks (0)
Share this content