IF text statements

[CRVDiva]

Please help!
I have a Pulldown menu in H8 with these choices: "Click Here to Rate",
"Requirements not met", "Requirements Met"

I need a formula or function statement that for this:
In H8 if "Requirements not met" is chosen, then I8 can't be left blank (must
contain an comment);
If "Click Here to Rate" or "Requirements Met" is chosen, then I8 can be left
empty.

I want the formula to require that comments have to be entered in I8 if H8
reads "Requirements not met".

Thanks

 

[Bob Phillips]

Use Data Validation, Custom with a formula of =OR(H8<>"Requirements not
met",LEN(I8)>0), and uncheck Ignore blank.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

In I8 enter =IF(H8="Requirements not met","")


Gord Dibben Excel MVP

 

[CRVDiva]

This worked perfectly! Thanks!!

Use Data Validation, Custom with a formula of =OR(H8<>"Requirements not
met",LEN(I8)>0), and uncheck Ignore blank.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

One thiing I discovered. If the user hits cancel, the box will go away and
they can move on without entering anything when they select requirements not
met. They can also move on if they arrow past I8 or even tab past it. Is
there away to prevent this?

....

If you need users to make entries in a specific order, then you're going to
have to use VBA and event handlers, though you'd be better off creating your
own dialogs (aka user forms) with your desired tab order.

 

[DOR]

Or, if you don't want to take the user form route, you could
prepopulate I8 with "Enter a reason here" with font color set to the
background, thereby making it invisible. Use conditional formatting to
change I8 format to, say red background and yellow font color, if the
condition

=AND(I8="Enter a reason here",H8="Requirements not met") is met.

Add a second condition to change the background back to normal and the
font color to black if the condition

=I8<>"Enter a reason here" is met.

This will at least highlight I8 as needing attention, and leave it
highlighted until the user enters something. The users can still
ignore it, but if you make the colors ugly enough, you may get their
attention!

Ideally, you would set up a set of named messages like Msg1, Msg2, etc.
in one place, and use the names in the formulas, so that if you needed
to change the messages, you could do so in one place and they would
take effect in all formulas.