Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF text statements
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 |
#2
|
|||
|
|||
IF text statements
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) "CRVDiva" wrote in message ... 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 |
#3
|
|||
|
|||
IF text statements
In I8 enter =IF(H8="Requirements not met","")
Gord Dibben Excel MVP On Mon, 31 Oct 2005 09:28:53 -0800, "CRVDiva" wrote: 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 |
#4
|
|||
|
|||
IF text statements
This worked perfectly! Thanks!!
"Bob Phillips" wrote: 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) "CRVDiva" wrote in message ... 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 |
#5
|
|||
|
|||
IF text statements
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? Thanks! "Bob Phillips" wrote: 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) "CRVDiva" wrote in message ... 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 |
#6
|
|||
|
|||
IF text statements
"CRVDiva" wrote...
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. |
#7
|
|||
|
|||
IF text statements
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Text Wrapping | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |