Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: If Function with data validation

  1. #1

    If Function with data validation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    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.


  3. #3

    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
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    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".


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

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Try this conditional formula after selecting column L:

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


  7. #7

    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 by nitum; 2014-02-07 at 04:16 PM.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    In my test, column N was not highlighted for that column...
    Attached Files Attached Files


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

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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •