IF EITHER OF 3 CELLS IN EXCEL IS NOT BLANK...

[Guest]

I am trying to do a mail merge in Word, my database is an
Excel file. I want to enter an IF field where "if either
one of these 3 Excel cells is NOT blank, enter text (888)
and if it IS blank, enter nothing"... can somebody help
me with the right formula?

 

[Peter Jamieson]

If you mean "if any of the 3 excel cells is not blank, use 888, but if they
are all blank, use blank, then try something like:

{ IF "{ MERGEFIELD cell1 }{ MERGEFIELD cell2 }{ MERGEFIELD cell3 }" = "" ""
"888" }

 

[Guest]

WOW, that's great help, I have two more scenarios that
relate:

1) Just like your response { IF "{ MERGEFIELD cell1 }{
MERGEFIELD cell2 }{ MERGEFIELD cell3 }" = "" "" "888" }

....AND CELL 4 reads "CA", then enter "testing"


2) IF CELL 1 AND CELL 2 ARE NOT BLANK, OR CELL 3 IS NOT
EQUAL TO 777, ENTER PHRASE "TESTING"

 

[Peter Jamieson]

Well, you really need to spell out /all/ the different possible cases,
because it isn't completely clear how the new cases relate to the situation
you already had. If you did that, you might find it easier to work out the
various nested statements for yourself - it's simple logic.

Also, if there are many more cases, it may make sense to try to generate the
text in your data source or, e.g. in an Access query.

e.g....

...AND CELL 4 reads "CA", then enter "testing"

Which of the following do you mean:
a. if cell1,2,3 are all blank but cell4 is CA, use "testing". In all other
cases, use 888
b. if one of cell1,2,3 is non-blank, use 888. In addition, if cell4 is
"CA", add "testing"
c. whatever the value of cell1,2,3, if cell4 is "CA", add "testing"
?

For (a), try:

{ IF "{ MERGEFIELD cell1 }{ MERGEFIELD cell2 }{ MERGEFIELD cell3 }" = ""
"{ IF "{ MERGEFIELD cell4 }" = "CA" "testing" "" }"
"888" }

For (b), try

{ IF "{ MERGEFIELD cell1 }{ MERGEFIELD cell2 }{ MERGEFIELD cell3 }" = ""
""
"888{ IF "{ MERGEFIELD cell4}" = "CA" "testing" "" }" }

For (c), try the obvious:

{ IF "{ MERGEFIELD cell1 }{ MERGEFIELD cell2 }{ MERGEFIELD cell3 }" = ""
""
"888" }{ { IF "{ MERGEFIELD cell4}" = "CA" "testing" "" }

2) IF CELL 1 AND CELL 2 ARE NOT BLANK, OR CELL 3 IS NOT
EQUAL TO 777, ENTER PHRASE "TESTING"

How does this case relate to the other cases you have described?

(NB, I'll be away for a few days).

 

[santi]

Thank you very much for such valuable info!! I have, I
promise, the last scenario I need help with:

If either CELL 1, CELL 2 or CELL 3 is NOT blank, OR if
CELL 4 is NOT EQUAL to 'Service Plan', enter "ok to
proceed", otherwise don't enter anything.

Thank you very much in advance, this has taken me about 4
days of research already!

 

[Graham Mayor]

Fellow MVP Greg Maxey has answered this in one of your other threads on this
subject - to avoid this disassociation is the reason for keeping related
messages in the same thread. No one objects to answering your questions in
any quantity, provided you supply the information required to enable us to
do so.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[LDanix]

I want to enter multiple "IF" statements for the same field. An example would be like IF cell1=XX type AA or cell1=YY type BB. What would be the proper syntax for entering this type of "either/or" "IF" statement. If that makes sense.

 

[Peter Jamieson]

In that case you could use

{ IF { MERGEFIELD cell1 } = "XX" "AA" "" }{ IF { MERGEFIELD cell1 } = "YY"
"BB" "" }

(because cell1 cannot be both XX and YY, so only one of the above can ever
be true.

Or if you prefer you can nest one IF inside another, e.g.

{ IF { MERGEFIELD cell1 } = "XX" "AA" "{ IF { MERGEFIELD cell1 } = "YY" "BB"
"" }" }

You can only nest to a certain number of levels (around 20).

--
Peter Jamieson

I want to enter multiple "IF" statements for the same field. An example

would be like IF cell1=XX type AA or cell1=YY type BB. What would be the
proper syntax for entering this type of "either/or" "IF" statement. If that
makes sense.

 

[LDanix]

Thanks for the info, Peter. I don't think that you answered this one, if you
did, please explain a little better. What would be the proper syntax to cause
Word to process something like {IF {MERGEFIELD cell1 } = "anything but XX"
"AA"} ?

 

[Peter Jamieson]

Well, the syntax is { IF condition "result if the condition is true" "result
if the condition is false" }

so in fact it's

{ IF { MERGEFIELD cell1 } = "XX" "" "AA" }

 

[Peter Jamieson]

All the {} in field expressoins need to be the special field braces that you
can insert using ctrl-F9. My guess is that you have used the ordinary ones
on the keyboard, but let us know if that isn't the case.

 

[Peter Jamieson]

That's not the usual way of doing it. You need to organise each "branch" of
the IF so that you always get the space that you need, e.g.

{ IF condition "true-result" "false-result" } more text

is the same as

{ IF condition "true-result " "false-result " }more text


(difficult to explain without a good example but I hope that will help
anyway)