#1
|
|||
|
|||
using if and compare with multiple criteria and ranges of values
Help!
I am working on a document that needs to fill in text that is dependent on amount ranges in a single mergefield. I have three ranges of values and text associated with each. The ranges are 0 - 417000 417001 - 650000 650001 and higher I can get two of the three to work but not all three at once. Here's what I have: { If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } < 417001 } ) } = 1 "text1" "" } { If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } > 417000 < 650001 } ) } = 1 "text2" "" } { If { = and ( { compare { mergefield_program } = "1/1" }, { compare { mergefield_dollaramount } > 650001 } ) } = 1 "text3" "" } I have tried multiple iterations of the < and > but nothing seems to work. Any suggestions? My head is starting to hurt from pounding it against this brick wall of a problem. |
#2
|
||||
|
||||
It's a whole lot simpler than that:
{IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}} I can't see where {MERGEFIELD program} has a role.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Quote:
The program field comes into play because I have several different programs that can also impact the text. 1/1 3/1 5/1 and 7/1. The program in conjunction with the amount determines the text that is used. the group of three alternatives i posted are repeated for each of the programs. |
#4
|
||||
|
||||
In that case, you could use:
{IF{MERGEFIELD program}= "1/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}} {IF{MERGEFIELD program}= "3/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}} {IF{MERGEFIELD program}= "5/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}} {IF{MERGEFIELD program}= "7/1" {IF{MERGEFIELD dollaramount \# 0}> 650000 "text3" {IF{MERGEFIELD dollaramount \# 0}> 417000 "text2" "text1"}}}
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Quote:
I've been bouncing off this problem for two days. Most of what I know about if then in Word is self taught from what I can find on the net so this is extremely helpful. Thanks again. |
#6
|
||||
|
||||
You might also want to check out the Sticky 'Mailmerge Tips & Tricks' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Running into an unexpected issue with this.
The solution works perfectly, except.... For each of the types I have, 1/2 3/1 and so on, they can be either plain 2/1 or 1/1 special. I need to be able to insert one group of text for the various amount ranges if it's a plain 1/1 and a different group of text baded on thevalue range if it's a 1/1 special. I've tried using various wildcards but nothing seems to work. |
#8
|
|||
|
|||
Figured it out!
I added a third condition to the solution already provided. {if {mergefield_program} = "1/1 ARM*" {if {mergefield_program}= "* - special"{if mergefield_amount}>650000 I then used the exact same syntax except the 2nd statement is "<> - special". That seems to do the trick. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I need to add multiple values based on multiple criteria in a cell not sure what to do | AUHAMM | Excel | 3 | 10-27-2014 09:11 PM |
Lookup multiple values and compare different scenarios to get a specific result | mws | Excel | 5 | 05-24-2014 04:52 AM |
transpose values based on mulitple criteria | mlttkw | Excel | 1 | 12-06-2013 02:00 AM |
Compare content of two ranges INCLUDING FORMATTING | delasson | Word VBA | 3 | 12-10-2012 03:54 AM |
Change values in cells based on criteria | SaneMan | Excel Programming | 2 | 02-02-2012 07:58 AM |