Conditional formatting with multiple IFs

[mucusboy]

I am trying to do some conditional formatting which I feel should be simple but I am getting nowhere


[TABLE="width: 380"]
<TBODY>[TR]
[TD]Product type</SPAN>
[/TD]
[TD]Code</SPAN>
[/TD]
[TD]Area</SPAN>
[/TD]
[/TR]
[TR]
[TD]X</SPAN>
[/TD]
[TD]1-A-3-008</SPAN>
[/TD]
[TD]W-1 Back26</SPAN>
[/TD]
[/TR]
[TR]
[TD]Y</SPAN>
[/TD]
[TD]3-B-1-023</SPAN>
[/TD]
[TD]W-1 Back23</SPAN>
[/TD]
[/TR]
[TR]
[TD]X</SPAN>
[/TD]
[TD]9-N-4-369</SPAN>
[/TD]
[TD]W-2 Front2</SPAN>
[/TD]
[/TR]
[TR]
[TD]N</SPAN>
[/TD]
[TD]7-Y-8-028</SPAN>
[/TD]
[TD]W-2 Back69</SPAN>
[/TD]
[/TR]
[TR]
[TD]Y</SPAN>
[/TD]
[TD]6-V-7-058</SPAN>
[/TD]
[TD]W-3 Left 78</SPAN>
[/TD]
[/TR]
[TR]
[TD]Y</SPAN>
[/TD]
[TD]2-S-5-014</SPAN>
[/TD]
[TD]W-1 Front7</SPAN>
[/TD]
[/TR]
[TR]
[TD]X</SPAN>
[/TD]
[TD]4-F-8-963</SPAN>
[/TD]
[TD]W-2 Right 8</SPAN>
[/TD]
[/TR]
[TR]
[TD]N</SPAN>
[/TD]
[TD]8-G-7-214</SPAN>
[/TD]
[TD]W-2 Front2</SPAN>
[/TD]
[/TR]
[TR]
[TD]B</SPAN>
[/TD]
[TD]3-B-1-089</SPAN>
[/TD]
[TD]W-2 Back69</SPAN>
[/TD]
[/TR]
[TR]
[TD]Y</SPAN>
[/TD]
[TD]9-N-4-769</SPAN>
[/TD]
[TD]W-3 Left 78</SPAN>
[/TD]
[/TR]
[TR]
[TD]H</SPAN>
[/TD]
[TD]7-Y-8-022</SPAN>
[/TD]
[TD]W-1 Front7</SPAN>
[/TD]
[/TR]
[TR]
[TD]V</SPAN>
[/TD]
[TD]6-Q-7-058</SPAN>
[/TD]
[TD]W-2 Right 8</SPAN>
[/TD]
[/TR]
[TR]
[TD]N</SPAN>
[/TD]
[TD]5-C-6-589</SPAN>
[/TD]
[TD]W-2 Front2</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


This is the data I am working with.

I am trying to get the row to highlight using conditional formatting IF column B contains "F" AND column C starts with "W-2". so in the case above the row I have made bold should get highlighted. I have tried he following to no avail:

=AND($b2="*-F*",$C2="W-2*")

=IF($B2="*-F*", IF($C2="W-2",TRUE,FALSE),FALSE)

I am not sure if it will take the * wildcard in conditional formatting or if I have used too many. Just lost the plot now.

I have tried various other conbinations along the way to no avail and searched the forum. Can anyone help???

 

[Norie]

You can't use wildcards with =, you'll need to use SEARCH (or FIND) to see if column B contains an F.

For column C you can use LEFT to extract the first 3 characters.

So combining the two try this formula for the conditional formatting.

=AND(ISNUMBER(SEARCH("F", $B8)), LEFT($C8,3)="W-2")

 

[mucusboy]

Hi Norrie,

Thanks for this but for some reason it is highlighting the 2^nd row I am not au fait with the SEARCH function so I am not confident how to manipulate this?

Also I wondered whether it will be possible to ‘scale up’ this formula as I am hoping to apply the formatting to around 1000 rows and highlight all of those which satisfy the criteria. Further to this there will be other conditional formatting looking for rows that start with “3” in column B and are is “w-1” for example. I don’t know if this will affect any formula so apologies I never said this originally.

Thank you got pointing out that the wildcard doesn’t work with an =. I would have wasted a lot more time If you hadn’t pointed that out

 

[mucusboy]

Anyone able to help with this, even if just to say it's not possible?

 

[Joe4]

Thanks for this but for some reason it is highlighting the 2^nd row I am not au fait with the SEARCH function so I am not confident how to manipulate this?

Norie's formula works for me.
Double-check to make sure that you have written the formula so that it is referencing the right now (i.e. did you highlight row 7 and use the number 8 in your formula)?

When applying to multiple rows, highlight all the rows that you want to apply it to, and then write the formula as it pertains to the first cell in your range.
Excel is smart enough to adjust to for all the other rows.

 

[mucusboy]

Thanks both for your help. I have now been able to apply this to the table I gave above and it is working great. I now have a slight issue where I am trying to apply it to my worksheet (I only put a simplified version above)


I have adapted the formula slightly so that the columns are correct as the data is in columns F and G:

=AND(ISNUMBER(SEARCH("B", $F7)), LEFT($C8,3)="W-1")

Unfortunately I think I have made an error somewhere as it doesn’t work for me. I am wondering whether it is something to do with the LEFT($C8,3 part of the formula as I am not 100% confident how this is working. I thought perhaps it was defining the column to look in which is why 3 would have looked in column C in the original formula? But when I change it to 7 it still does not work. Also not confident whether I need to change the LEFT bit, but no idea really how that is functioning.

Sorry I am being thick. Can anyone help?

 

[mucusboy]

Thanks both for your help. I have now been able to apply this to the table I gave above and it is working great. I now have a slight issue where I am trying to apply it to my worksheet (I only put a simplified version above)


I have adapted the formula slightly so that the columns are correct as the data is in columns F and G:

=AND(ISNUMBER(SEARCH("B", $F7)), LEFT($C8,3)="W-1")

Unfortunately I think I have made an error somewhere as it doesn’t work for me. I am wondering whether it is something to do with the LEFT($C8,3 part of the formula as I am not 100% confident how this is working. I thought perhaps it was defining the column to look in which is why 3 would have looked in column C in the original formula? But when I change it to 7 it still does not work. Also not confident whether I need to change the LEFT bit, but no idea really how that is functioning.

Sorry I am being thick. Can anyone help?

Sorry I meant to put G instead of C. Still doesn't work

=AND(ISNUMBER(SEARCH("B", $F7)), LEFT($G8,3)="W-1")

 

[tygrrboi]

your examples seem to be all over the place... Do you want W-1 or W-2?


Have you tried it with two search functions instead of a left function?

=AND(ISNUMBER(SEARCH("B", $F7)), ISNUMBER(SEARCH("W-1",$G8)))

 

[tygrrboi]

also it might be worth considering... as you mentioned also potentially searching for cells that start with 3...
Use the hyphen as part of the search. SEARCH("3-", $F7)

Because a simple SEARCH("3", $F7) would be true for 3-B-405 AND 8-C-302

 

[Joe4]

=AND(ISNUMBER(SEARCH("B", $F7)), LEFT($G8,3)="W-1")

In that formula, you are checking column F of row 7 and column G of row 8.
So, you are checking pieces of two different rows in one single formula. I doubt that is what you want to do.

Don't overthink it, it really is pretty straightforward. What is the first row you are applying this formula to? The formula should reference THAT row in both places.
So, if you were highlighting rows 4-100 to apply the Conditional Formatting formula for, you would write the formula like this:

[COLOR=#333333]=AND(ISNUMBER(SEARCH("B", $F[/COLOR][COLOR=#ff0000][B]4[/B][/COLOR][COLOR=#333333])), LEFT($G[/COLOR][COLOR=#ff0000][B]4[/B][/COLOR][COLOR=#333333],3)="W-1")[/COLOR]

So, those numbers in red should be the row number of the first row highlighted in your Conditional Formatting application.