+ Reply to Thread
Results 1 to 15 of 15

Complex Conditional Formatting

  1. #1
    Registered User
    Join Date
    03-30-2016
    Location
    2
    MS-Off Ver
    2010
    Posts
    26

    Complex Conditional Formatting

    I am having an issue with my conditional formatting. In worksheet WSOC 1.0 you can see that I have some CF set in D6, G6, J6, M6, D11, G11, J11, and M11. My issue is, whenever I put 15 for reps, and a percentage below 50.8 my CF defaults to a light blue, which should only occur if the reference cells contain "VERY LIGHT" in them... Problem is the reference cells (D275:M408) may say MEDIUM or MEDIUM LIGHT, depending on reps and %.

    You can see an example of this in cell M6.

    SO, reference cells are correct. CF is the same as for other cells. Result is off given this specific rep range. It seems to function fine with other rep ranges and %.

    Using Excel 2010.
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,679

    Re: Complex Conditional Formatting

    Quote Originally Posted by sh1483 View Post
    ...my CF defaults to a light blue, which should only occur if the reference cells contain "VERY LIGHT" in them... Problem is the reference cells (D275:M408) may say MEDIUM or MEDIUM LIGHT, depending on reps and %.

    You can see an example of this in cell M6.
    M6 is configured as Light Blue by default. That is, if there were no CF the cell would be light blue (click on the fill bucket and you will see what I mean). The CF rule references M275, which is MEDIUM and there is no rule for CF. So the cell is light blue.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-30-2016
    Location
    2
    MS-Off Ver
    2010
    Posts
    26

    Re: Complex Conditional Formatting

    I see that the cell M6 is configured as light blue, but so are D6,G6, & J6 with no ill effects. I don't think that having a default fill color overrides CF. While it is true that there is no CF for M275, the CF for D:M6 states that the color of cells should be based upon the words contained in D:M275 respectively.

    So if CF for M6 states that
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    with the format set to light green for a True finding, then M6 should format in Light Green since M275 contains the text MEDIUM.

    Even if I set the default fill them to No Fill, I still net the same error, just with no fill in the cell instead of the correct fill based on the CF rules. Again, this only happens when the reps are 15 and the percentage is less than 50.8
    ... I appreciate your assistance, and I can see how you are confused by what is happening, because I sure am!

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,679

    Re: Complex Conditional Formatting

    Quote Originally Posted by sh1483 View Post
    So if CF for M6 states that
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    with the format set to light green for a True finding, then M6 should format in Light Green since M275 contains the text MEDIUM.
    And that's your problem. There is no such CF rule for M6. There are many rules for "Medium" -> light green but none of them apply to M6.

  5. #5
    Registered User
    Join Date
    03-30-2016
    Location
    2
    MS-Off Ver
    2010
    Posts
    26

    Re: Complex Conditional Formatting

    I don't think I understand what you are saying. When I click on M6 to select it, Go to Conditional Formatting>Manage Rules it shows me that I have set 9 different CF rules for the selected cell, even telling me that the CF rules are applied to cells M6:O6. Since M6:O6 are a merged cell, I have referred to it as M6 here.

    The only CF rule that turns the cell Light Blue is if M275 contains "VERY LIGHT". M275 contains a formula that looks up corresponding information in the worksheet Prilepin (and does so correctly).

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,718

    Re: Complex Conditional Formatting

    The formula in M275 returns MEDIUM. There is no rule for MEDIUM for M6, only MEDIUM HEAVY and MEDIUM LIGHT, so M6 has no CF fill. The blue fill you see has been applied manually, not via the CF rule.
    Last edited by AliGW; 07-15-2017 at 01:27 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,679

    Re: Complex Conditional Formatting

    Quote Originally Posted by sh1483 View Post
    I don't think I understand what you are saying. When I click on M6 to select it, Go to Conditional Formatting>Manage Rules it shows me that I have set 9 different CF rules for the selected cell, even telling me that the CF rules are applied to cells M6:O6.
    Here are your 9 rules for cell M6:

    =M275="TOO LIGHT"
    =M275="VERY LIGHT"
    =M275="LIGHT"
    =M275="MEDIUM LIGHT"
    =M275="MEDIUM HEAVY"
    =M275="HEAVY"
    =M275="VERY HEAVY"
    =M275="MAX"
    =M275="TOO HEAVY"

    There is no rule that says
    =M275="MEDIUM"


    So when M275 contains "MEDIUM", which is the case for the file you attached, the cell takes its default color, which is light blue.

  8. #8
    Registered User
    Join Date
    03-30-2016
    Location
    2
    MS-Off Ver
    2010
    Posts
    26

    Re: Complex Conditional Formatting

    I appreciate you dumbing it down for me; I understand what y'all have been trying to tell me. I made the change...

    Unfortunately, the other cells did contain a rule for "MEDIUM", and also would somehow default to light blue if the 15, 50.7% parameters were input. Even after making the change in M6 the issue is still not resolved.

    I have attached a copy of the file, with the "MEDIUM" change applied, and you can see the same problem. If you change F4 to 15 and D6 to 50.7 or below you will run into the same issue.

    Within Prilepin AF2:DI15 there is no provision for a percentage less than 40. Could this be a part of the problem? I would think even with the table as it currently sits that the cells would not default to Light Blue unless the %age was less than 40.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,718

    Re: Complex Conditional Formatting

    No, you haven't fully understood. Please see the attachment, which explains the issue. You need to change the default fill to no colour for all affected cells.
    Attached Images Attached Images
    Last edited by AliGW; 07-15-2017 at 12:18 PM.

  10. #10
    Registered User
    Join Date
    03-30-2016
    Location
    2
    MS-Off Ver
    2010
    Posts
    26

    Re: Complex Conditional Formatting

    I understand, and understood before.

    The problem is that the criteria ARE being met in this instance. M275 is returning "MEDIUM" because the intersect of the criteria 15 Reps and 50.7 Percent in Prilepin lead to somewhere between MEDIUM HEAVY and MEDIUM. Because the INDEXMATCHMATCH is set to 1 (Less Than), it looks for the next lowest percent if the criteria are not matched exactly. This is why the return is MEDIUM, even though the percentage entered isn't 49.

    Since M275 contains "MEDIUM", and there is now a CF rule set that states =M275="MEDIUM" format Light Green, the cell should be showing as Light Green.

    If it is showing as Light Green on your copy, on your computer, then I don't think that there IS a possible solution, because it is showing up Light Blue on my copy, on my computer.

    I believe that if you enter a few different scenarios in O4 and M6, you will see that the CF seems to work as it should, except when 15 is entered in M6, and a %age between 50.7 and 40 are entered in O4. You can cross-reference with Prilepin to make sure that everything is how it should be.

    Even if I select Stop If True I still run into the same issue.

    Maybe it is just me.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,718

    Re: Complex Conditional Formatting

    Since M275 contains "MEDIUM", and there is now a CF rule set that states =M275="MEDIUM" format Light Green, the cell should be showing as Light Green.
    Yes, but that rule is applied to this range: =$M$6:$O$6

    So it's not going to have any effect on M275, whatever is in that cell.

    EDIT: Ah - hang on! I think I'm on to something ...
    Last edited by AliGW; 07-15-2017 at 12:56 PM.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,718

    Re: Complex Conditional Formatting

    I've realised what's wrong. The entries on your Prilepin tab have trailing spaces, so what is in cell M275 is not "MEDIUM", but "MEDIUM ". That's why it's not working for you - there isn't a match.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,718

    Re: Complex Conditional Formatting

    Change the formula in M275 to this:

    =TRIM(INDEX(Prilepin!$AF$2:$DI$15,MATCH('WSOC 1.0'!O4,Prilepin!$AF$2:$AF$15,1),MATCH('WSOC 1.0'!M6,Prilepin!$AF$2:$DI$2,1)))

  14. #14
    Registered User
    Join Date
    03-30-2016
    Location
    2
    MS-Off Ver
    2010
    Posts
    26

    Re: Complex Conditional Formatting

    YES! Thank you so much for your patience!

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,718

    Re: Complex Conditional Formatting

    Glad we got there in the end!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Complex Conditional Formatting
    By mhcoats in forum Excel General
    Replies: 2
    Last Post: 11-03-2008, 03:31 PM
  2. Complex Conditional Formatting
    By satchmo in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-03-2007, 12:11 PM
  3. Replies: 6
    Last Post: 03-12-2006, 06:30 PM
  4. complex?? Q about Conditional formatting
    By AngelaG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 PM
  5. complex?? Q about Conditional formatting
    By AngelaG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  6. [SOLVED] complex?? Q about Conditional formatting
    By AngelaG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  7. complex?? Q about Conditional formatting
    By AngelaG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  8. complex?? Q about Conditional formatting
    By AngelaG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1