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.

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:

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!

Originally Posted by sh1483

So if CF for M6 states that

Formula:

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.

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).

Originally Posted by sh1483

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.

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.

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.

YES! Thank you so much for your patience!