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

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
Jan 27, 2014
Messages
10
Reaction score
0
Points
0
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

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
Jan 27, 2014
Messages
10
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Try this conditional formula after selecting column L:

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

nitum

New member
Joined
Jan 27, 2014
Messages
10
Reaction score
0
Points
0
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:

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
In my test, column N was not highlighted for that column...
 

Attachments

  • Tracker.xls
    39.5 KB · Views: 22

nitum

New member
Joined
Jan 27, 2014
Messages
10
Reaction score
0
Points
0
Now its working..
May be i wrongly use the formula.
Thanks very much for the help
 

nitum

New member
Joined
Jan 27, 2014
Messages
10
Reaction score
0
Points
0
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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=""
 
Top