If Function with data validation

[nitum]

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

[NBVC]

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.

[nitum]

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

[NBVC]

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

[nitum]

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

[NBVC]

Try this conditional formula after selecting column L:

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

[nitum]

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

[NBVC]

In my test, column N was not highlighted for that column...

[nitum]

Now its working..
May be i wrongly use the formula.
Thanks very much for the help

[nitum]

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