IF / OR Function

[GillianX]

Hi all, hope someone can help with this - it's driving me crazy.

Have a straight forward list of cells, name, address, company name etc,
which requires the user to type in text or select from a drop down list to
complete. Simple enough. I have the following formula which appears in a
separate box if any of the required fields are left blank. However, when a
user first enters the sheet, this message appears automatically because all
the fields are already blank.

=IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13="",B15=""),"Please ensure
all fields are completed","")

What I would really like to do is have this message appear at the end of
non-completion of cell B15 rather than be visible all the time.

Hope someone can help.

Many thanks.

 

[John C]

Assuming all cells will have some value/text in them, you could try:

=IF(B15="","",IF(OR(B3="",B5="",B7="",B9="",D9="",B11="",B13=""),"Please
ensure all fields are completed"",""))

Hope this helps.

 

[GillianX]

That's the thing, some of the cells might not have text (if the user, say,
overlooks a field) so that's why I need a "please ensure all fields are
completed" message so that they do get completed and aren't left blank.

Hope you can help.

 

[John C]

If all the fields are 'supposed' to have text, then my formula will work.
Essentially, once B15 is populated with anything, it will then check to see
if any of the other fields are blank. If any of the other fields are blank,
it will post the message, if all of the other fields have something in there
(be it a number, text, etc), then nothing will display (as everything has
been populated).

My suggestion, try out the formula I gave you, and let us know if it works.

 

[GillianX]

Thanks John C that works!

If all the fields are 'supposed' to have text, then my formula will work.
Essentially, once B15 is populated with anything, it will then check to see
if any of the other fields are blank. If any of the other fields are blank,
it will post the message, if all of the other fields have something in there
(be it a number, text, etc), then nothing will display (as everything has
been populated).

My suggestion, try out the formula I gave you, and let us know if it works.

 

[John C]

You're welcome, and thank you for the feedback.

 

[GillianX]

Just to ask, is it possible to utilise this formula for only one entry e.g.

If cell B19 is left blank, then cell B20 is populate with a "Please select
an option" response?

 

[John C]

=IF(B19="","Please select...","")
The second "" can be whatever you wish it to be, including a vlookup.