Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    multiple conditions in Conditional format

    How do I use 2 conditional formats in column B so: Sample attached.

    A) Format #1
    If no end date [B],
    then format #1
    I use "cell value is equal to 0"
    Fine, now the problem:

    B) Format #2 ONLY if:
    1) a start date
    2) AND no end date,
    3) and D3 <0,
    then format #2 [B4]

    How do I contain all these conditions in a conditional format for Format #2?

    I need to avoid evoking format 2 in B5,6,7,etc:
    If no start date [and no end date] and D3 <0
    It should be format 1, not format 2

    Essentially, all cells in column B that have no date should have format 1 [B5,6,7,etc].
    Format 2 is evoked only if a start date exists, has no end date and that the avg [D3] has been exceeded [<0]. [B4]
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You must test your format2 first, then format 1, since if cond1 is true whenever cond2 is true and once cond1 is found to be true, cond2 is never tested (presuming you are pre-XL2007
    Cond 1 is the formula:
    =AND(A3>0,B3=0,D3<0)

    Cond2 is the formula:
    =B3=0

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    Thanks. I was trying to do it with "IF".
    Can we add a 4th condition? See attachment.
    The 4th condition is in column C and is A,B,C or D.
    A-D have their own avg-today() in column F and are labeled in column E.

    I need CF to pick the proper value/cell in column F to associate with the current/working cell in column B.
    In the attached example, I need CF to pick F5 for formatting C4 because the 4th condition is "C".
    The actual spreadsheet will not have the cells aligned as in the example.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You don't need an if for CF. An IF is for changing the output based on a TRUE/FALSE result. The CF works on a True/False directly. True meets the condition and formats, false does not and then goes to the next condition.

    Do you want a 4th conditional format? The answer is no for XL97-2003. It is yes for XL2007 /2010.

    DO you want a 4th condition in the AND. That is a YES. But I don't understand from your remarks what you want it to be. Could you elaborate on what colors you want the what cells to be and when they should be that color. [In XL<2007 you are allowed 4 colors: explicit when No CF or all CFs are false or a color for each of 3 possible conditions.]

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    I want a 4th condition in the AND.
    Forget the color, I used it only to highlight the cells in question.
    I have changed the strings in columns C and E to avoid confusion, ABCD is now WXYZ. See attached Sample #3.xls
    The original solution you gave me was based on a single 3rd condition ["avg - today()"], D3. original Sample.xls
    =AND(A4>0,B4=0,D3<0)

    What if I have 4 different values for 4 different "avg - today()" in column D, [D3, D4, D5, D6].
    Move these to column F, [F3, F4, F5, F6]. Sample#2.xls

    There are 4 items that have their own "avg - today()" or value in column F
    These are labeled in column E [W,X,Y,Z] to differentiate.
    Item W (or "avg -today()"#1) [with label E3 or W] is in F3
    Item X (or "avg - today()"#2) [with label E4 or X] is in F4
    Item Y (or "avg - today()"#3) [with label E5 or Y] is in F5
    Item Z (or "avg - today()"#4) [with label E6 or Z] is in F6
    Each starting date is for one of these items [W,X,Y,Z] which is labeled/assigned in column C. Column C labels the item associated with its corresponding B to its left.

    Now I need to match the cell in column B in question [B4] with the CORRECT "avg - today()" or correct row in column F [F3,F4,F5,F6]. [F5]

    This is what I refer to as the 4th condition, matching the cell "Cx" with the corresponding "Fx".

    B4 is an item "Y" and the "avg - today()" labeled "Y" is F5. Therefore the value we need to use as the last condition or the ORIGINAL D3 is F5.
    For B4 =AND(A4>0,B4=0,F5<0)

    For B3 =AND(A4>0,B4=0,F3<0)
    For B5 =AND(A4>0,B4=0,F3<0)
    For B6 =AND(A4>0,B4=0,F6<0)
    For B7 =AND(A4>0,B4=0,F4<0)

    How do I program CF to make that determination or match. It seems to be sort of a VLOOKUP or MATCH function to correctly match column B with column F.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Forgot attachment. See attached.
    Attached Files Attached Files

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand you are asking for (locked on A4 and B4)
    =AND($A$4>0,$B$4=0,VLOOKUP(C3,$E$3:$F$6,2,0)<0)

    Though I suspect you want in B3:
    =AND(A3>0,B3=0,VLOOKUP(C3,$E$3:$F$6,2,0)<0)

    And in B4:
    =AND(A4>0,B4=0,VLOOKUP(C4,$E$3:$F$6,2,0)<0)

    etc (with A and B not locked on the cell, but relative to the row you are on...
    Steve

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    skipro (2011-06-23)

  9. #8
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    If I understand you are asking for
    Exactly.
    I now see the syntax for Vlookup [and AND] in CF and Vlookup in AND.
    Thanks.

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You are very welcome.

    Steve

  11. #10
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    I have a more complex format to use this. See attached Sample #4.
    The "avg-today()" in cols E&F in previous sample #3 is replaced in sample #4 with
    a new format.
    Instead of the items listed in E and their values in F, now the items and values
    are listed in E but on different rows. I think I have that resolved. Please add
    any suggestions to my formula. See B3. I modified E4 to show that it works
    without the complication below.

    My problem is that the item is named [string#1] in C, but the matching item
    [string#2] in E, is a phrase which contains the item name [string #1].
    string#1 C4 = "Y"
    string#2 matching E13 = "if Y"
    How do I get "Y" matched to "if Y"?

    My thought is to use something like =RIGHT(E12,LEN(E12)-FIND(" ",E12,1)) to
    return "Y" from "if Y" but it appears it needs to be less specific and cover the
    range and also I do not know how/where to incorporate it in
    =OFFSET(INDEX(E3:E17,MATCH(C3,E3:E17,0),1),1,0).
    Attached Files Attached Files

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:

    =AND(A3>0,B3=0,INDEX(E3:E17,MATCH("if "&C3,E3:E17,0)+1)<0)

    Note: You don't need the "offset", you can just index one more than the match to get the same thing..

    Steve

  13. #12
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    Mucho gracias. As usual, superb.
    Is there a way to copy a thread in this forum other than the obvious copy/paste? The forum should have a copy/print function but I do not see it. I would think people would want a copy of threads.

  14. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I can do file- print from the browser. With Printpreview I can adjust the size...

    Steve

  15. #14
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by skipro View Post
    Steve,
    Mucho gracias. As usual, superb.
    Is there a way to copy a thread in this forum other than the obvious copy/paste? The forum should have a copy/print function but I do not see it. I would think people would want a copy of threads.
    If you have a PDF maker, you could use that to create PDF files of threads of interest.

    I use PDF Create (paid) and Bullzip (free) for such purposes.
    BullZip is here .... http://www.bullzip.com/products/pdf/info.php



Posting Permissions

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