If Function with data validation

nitum

New member
Joined
Jan 27, 2014
Messages
10
Reaction score
0
Points
0
Hi guys,

I've got a basic spreadsheet (attached) with data validation and depending on what is input into a certain cell, I wanted a few different things to happen:

1. If Column C has value Completed then Column F should have a value.. If F is blank then it should be display in Colour or the cell highlighted
2. If Column C has value Completed, then Column F should have Value, then Column K should have value... else display column F in colour or highlighted
3. IF Column B is Execute Test Case then column N should contain value else display in color for column N.

Thanks in advance
N
 

Attachments

  • Track.xls
    24.5 KB · Views: 14
I assume you are using Excel 2003:

Select column F and go to Format|Conditional Formatting.

Select Formula Is and then enter formula:

=AND($C2="Completed",OR($F2="",$K2=""))

click Format and choose from Pattern tab.

click Ok.

Now click column N and similarly go to Format|Conditional Formatting, then Formula Is..

=AND($B2="Execute Test Case",N2="")

click Format and choose from Pattern tab.
 
still getting problem

hi am still getting difficulty
Can someone please help?
Actually I am getting problem to insert Conditional Formatting.
Column N( No of test case created) should be highlighted if one of the
condition is not met
Column B should be Write Test Case
Column D should be completed
Column L should not have a Date
 

Attachments

  • Tracker.xls
    27 KB · Views: 13
So, in the sample should all be hightlighted since each line violates one of those conditions.

although the last condition is not clear... if L has a date, should N be hightlighted or only if L is blank?

In your sample all of column L has dates except one, but in that row column B is not "Write Test Case".
 
what i mean is that if L is blank then N should be highlighted..
In simpler terms
If Column B has value Execute Test Case and
Column D has value completed and
Column L has a date then
Column N should contain value else display in color for column N.

If one of the conditions fails then Column N should be display in colour..
Hope it clear now..
Thanks
 
Try this conditional formula after selecting column L:

=AND(B1="Execute Test Case",D1="Completed",L1<>"",N1="")
 
query

Hi the above query is good
but if the column L is left blank Column N should not be highlighted..
what I means is that if

B1 has value Execute Test Case
D1 has value Completed
L1 does not have a value then N1 should not be highlighted..

which implies that if 1 of the above condition is not met then N1 should not be highlighted..

it a bit difficult i know...
 
Last edited:
In my test, column N was not highlighted for that column...
 

Attachments

  • Tracker.xls
    39.5 KB · Views: 22
Now its working..
May be i wrongly use the formula.
Thanks very much for the help
 
Now its working..
May be i wrongly use the formula.
Thanks very much for the help

Hi
once again sorry to bother u
what query i can insert or conditional formating :
if column N has a value then Column M cannot have a value or vice versa
i.e if M contain Value then N cannot contain value or vice versa
 
If you select from N3 to the bottom of the range, then go to Data|Validation.

Select Custom from the drop down menu.

Then enter formula: =M3=""

You can select the Error Alert tab to write a personalized message to alert user what the error is about.

Click Ok.

Then select M3 to bottom and repeat procedure, changing formula to: =N3=""
 
Back
Top