Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    conditional format help

    Formula is:
    =IF(B2="","",IF(ISERROR(DATEDIF(B2,C2,"d")),"no end
    date",DATEDIF(B2,C2,"d")))

    I want to Conditional format so when:
    "D" has a # return it is yellow
    DATEDIF(B2,C2,"d"
    "D" returns "" it is white
    IF(B6="",""
    "D" returns "no end date" it is green
    IF(ISERROR(DATEDIF(B2,C2,"d")),"no end date"

    My CF is:
    Cell value >0 = yellow
    Cell value ="" = no color
    Cell value ="string" = green

    Results = All cells are yellow.

    CF works correctly if I rearrange the order to:

    Cell value ="" = no color
    Cell value ="string" = green
    Cell value >0 = yellow

    I assume that the order determines priority, but why would the >0
    affect, or interfere with, the strings/null? Is a string or null
    string < or >0? Does it have any numeric value?

    How can I change "Cell value >0" so I can place it first and have
    this work. Stated another way, what can I use so it would not
    interfere with the other two?

    Question #2 - Column E
    I have a formula which returns a string "no $C$5" if B5 has a date
    but C5 is empty. It changes as the rows change, that is the next row
    would say "no $C$6" if B6 has a date but C6 does not.

    =IF(B43="","",IF(ISERROR(DATEDIF(B43,B44,"d")),"no "&" "&CELL("address",B44),DATEDIF(B43,B44,"d")))

    How can I CF this return without having to do each row separately
    using the specific string return [no $C$5, no $C$6, etc] or without
    using ">than $C$1" ?
    I want to know other ways to do it maybe using a variable or other means to change the number(x) [$C$x] with corresponding change of the row.

    See attached sample.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Will this work for Column D? It uses a CF test of isnumber for first condition.
    If yes, just use the Format Painter for any additional rows of data in column D.
    Attached Files Attached Files

  3. The Following User Says Thank You to tfspry For This Useful Post:

    skipro (2011-04-02)

  4. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    This might work for Col D and Col E

    Let me know


    PS: Copy and Paste should work in both columns.
    Attached Files Attached Files
    Last edited by tfspry; 2011-04-01 at 22:03. Reason: added a PS:

  5. The Following User Says Thank You to tfspry For This Useful Post:

    skipro (2011-04-02)

  6. #4
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    tfspry,
    Thanks.
    First suggestion works great, second one works but will color all cells with no number. I want only the cell with the string return colored.
    Can a variable or a wildcard be used in CF? Can it be done in this case?

  7. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi skipro- Will the attached do what is needed?

    HTH
    Attached Files Attached Files

  8. #6
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Yes, thanks.
    I am still looking for a more direct solution if anyone knows how. I am curious if a variable, wildcard or other method can be used in CF.

  9. #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
    I am still looking for a more direct solution if anyone knows how.
    I don't understand what you mean by "more direct solution". The solutions provided seemed direct to me. What do you consider not direct about them?

    I am curious if a variable, wildcard or other method can be used in CF.
    Variables can be used, wildcards can be used, and there are many other methods. What you asked for did not require them. Do you have a simple example that you think requires a wildcard or a variable?

    Steve

  10. #8
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    SDCKPR,
    Thanks for responding.
    Answer to your 2nd question:
    Although my sample does not "require" the use of variables, wildcards or a different approach, I would be interested to see how they can be used for future reference. Can you, or someone else, show me how a variable, a wildcard and other approaches would work either in my sample, in general or lead me to references.

    Answer to your 1st question:
    I am only referring to Column "E" in my sample.
    If, as you state, variables, etc can be used, I assume this would be more direct, efficient and customizable.

    I see the offered solutions as functional for this sample but indirect and less efficient. A more direct solution would state:

    1) If numbers exist, do "A"
    2) If any variable of string $C$x[x as number] do "B"

    and do so with the least rules, be specific to this string needs and be available as a standard which would be more universal and useable if the need is reasonably modified.
    Rule 1= {Isnumber} seems to be an effective approach.
    Rule 3= {Cell Value Is Not equal to 0} works but is an indirect approach to the
    string issue, an elimination approach and general.
    Rule 2= {Formula is =Bx=""} again works, but is only needed to override Rule 3 because of Rule 3's lack of specificity.

    To me, this is less direct or definitive and less efficient.
    I recognize I did not state this originally and my choice of words describing
    my goals was not accurately descriptive. I am not suggesting the replies are
    inadequate for my sample, but rather I am seeking another and, as I interpret it, [here I go again], more direct approach.

    I am seeking
    A) Using only 2 rules which would be simpler and saving the 3rd, for future
    use, avoiding Excel's limitation. [I am using Excel 2003]
    B) [referenceing "2)" above]
    Only the specified string [$C$x] or its cousins[modified] would {do "B"}.
    The solutions offered are not string [or it's variations] specific. If future
    needs required another string to also be present, the solution would not
    perform as needed.

    That is what I meant as more direct or definitive. I am seeking an approach I
    can use for future unknown but similar needs which through the use of
    variables, wildcards or other methods/functions would expand the possibilities while being as direct/efficient as possible.

    I use this forum not only to solve a specific issue, but to also learn. That
    is why I asked the questions in the original post and why I am pursuing this
    further and asked the question in the April 2 reply.
    I hope I have clearly stated my objectives.

  11. #9
    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
    ...........
    I see the offered solutions as functional for this sample but indirect and less efficient. A more direct solution would state:

    1) If numbers exist, do "A"
    2) If any variable of string $C$x[x as number] do "B"
    ..........

    I am seeking
    A) Using only 2 rules which would be simpler and saving the 3rd, for future
    use, avoiding Excel's limitation. [I am using Excel 2003]
    The attached will work with just 2 CF's.
    Attached Files Attached Files
    Last edited by tfspry; 2011-04-03 at 19:33. Reason: added file

  12. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    There are several optional Conditional Formats that can be used.

    Using file in Post #9 for example: Condition #2 could be ...
    Formula is =left(E6,1)>="a"

    Just need some examples of variations you would like to use.

  13. The Following User Says Thank You to tfspry For This Useful Post:

    skipro (2011-04-04)

  14. #11
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Tfspry,
    Thanks. Both work fine and taught me another approach to use. I appreciate the 2 variations as it shows me how the concept can be manipulated.
    The first formula is more specific than the second one and has less chance of overlap with other string returns, which is my goal. I modified it to be even more specific and essentially eliminate the chance of overlap with another string return with:
    =LEFT(E5,6)="no $C$"
    This is a direct approach I have been seeking. Although it does not actually use a variable or wildcard, it does conceptually. To me, its specificity makes it more elequent.

    Can characters be skipped?
    Ex: Can you use character 3-5, skipping characters 1&2?
    Can you suggest any other approaches? Even suggestions to other functions would be helpful.

  15. #12
    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

    Can characters be skipped?
    Ex: Can you use character 3-5, skipping characters 1&2?
    Can you suggest any other approaches? Even suggestions to other functions would be helpful.
    To get characters 3-5, you could use =MID(E5,3,3)

    Here is a Microsoft site for some Excel 2003 Functions.....
    List of Excel functions (by category)

    Among others, take a look at the Text functions.

  16. #13
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Tfspry,
    Thanks.

  17. #14
    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
    Another way to use wildcards with cond formatting is to use countif, for example:
    =COUNTIF(e5,"=*He*lo*")>0

    will be true for cells with "He" in it followed by "lo" elsewhere in the cell...

    Steve

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

    skipro (2011-04-06)

  19. #15
    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
    Tfspry,
    Thanks.
    skipro - Your welcome. Glad we could help.

    There are so many possible functions/combinations that can be used in Conditional Format. Feel free to ask for more solutions for your ideas.

    Here is another Conditional Format (For condition #2) to test for characters in position 4-6 ($C$ ) in Col E......
    =MID(E5,4,3)="$C$"
    Last edited by tfspry; 2011-04-04 at 22:19. Reason: added (For condition #2)

  20. The Following User Says Thank You to tfspry For This Useful Post:

    skipro (2011-04-06)

Page 1 of 2 12 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
  •