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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

- 13 Jul 2004 17:37 GMT

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?

Reply to this Message

Peter Jamieson - 13 Jul 2004 19:02 GMT

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" }

Signature

Peter Jamieson

> 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?

Reply to this Message

- 13 Jul 2004 20:56 GMT

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"

>-----Original Message-----
>If you mean "if any of the 3 excel cells is not blank, use 888, but if they
[quoted text clipped - 10 lines]
>
>.

Reply to this Message

Peter Jamieson - 14 Jul 2004 09:28 GMT

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).

Signature

Peter Jamieson

> WOW, that's great help, I have two more scenarios that
> relate:
[quoted text clipped - 26 lines]
> >
> >.

Reply to this Message

santi - 15 Jul 2004 17:08 GMT

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!

>-----Original Message-----
>Well, you really need to spell out /all/ the different possible cases,
[quoted text clipped - 74 lines]
>
>.

Reply to this Message

Graham Mayor - 16 Jul 2004 06:40 GMT

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.

Signature

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Thank you very much for such valuable info!! I have, I
> promise, the last scenario I need help with:
[quoted text clipped - 85 lines]
>>
>> .

Reply to this Message

LDanix - 05 Aug 2004 22:07 GMT

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.

> 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:
[quoted text clipped - 7 lines]
> > and if it IS blank, enter nothing"... can somebody help
> > me with the right formula?

Reply to this Message

Peter Jamieson - 05 Aug 2004 23:30 GMT

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).

Signature

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.

> > 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:
[quoted text clipped - 7 lines]
> > > and if it IS blank, enter nothing"... can somebody help
> > > me with the right formula?

Reply to this Message

LDanix - 06 Aug 2004 01:25 GMT

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"} ?

> In that case you could use
>
[quoted text clipped - 29 lines]
> > > > and if it IS blank, enter nothing"... can somebody help
> > > > me with the right formula?

Reply to this Message

Peter Jamieson - 06 Aug 2004 10:18 GMT

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" }

Signature

Peter Jamieson

> 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
[quoted text clipped - 34 lines]
> > > > > and if it IS blank, enter nothing"... can somebody help
> > > > > me with the right formula?

Reply to this Message