HELP - Formula to highlight row if cell contains specific text

[simi_uk]

Hi All,

looking for some help here as i'm going mad trying to get CF to do it for me properly.

I have a single sheet of batch controlled warehouse items & their locations. This sheet is used to track when the main location holding the stock (for example 025E0013A - Rack 23, Shelf E, Bin 13A) becomes empty, when this happens A is removed from the sheet, however I am then left having to search for the secondary bin location (025E0013B) to see if stock is available here.

Can anybody suggest anything that might help? The location column contains all values from 025A0001A thru 025E0065B and 026A0001A thru 026E0065B. What i need it to do is pick out all the bin locations ending with 'B' if the primary 'A' bin is not present - make sense?

Thanks all in advance.

Simi

 

[Gerald Higgins]

Let's be clear.
Do you want to highlight 026E0065B only if 026E0065A is NOT found ?

 

[simi_uk]

Let's be clear.
Do you want to highlight 026E0065B only if 026E0065A is NOT found ?

correct - the 'B' Location should only be highlighted if the 'A' Location is NOT found...

example:
if 025A0012A and 025A0012B are found, nothing is highlighted.
if only 025A0012B is found, then the entire row is highlighted.

 

[GlennUK]

One solution ( of many, I'm sure ) should be something similar to this as a condition in the CF:

=AND(RIGHT(A2,1)="B",ISNA(MATCH(LEFT(A2,8)&"A",$A$2:$A$44,0)))

... adjust the column references to suit.

 

[simi_uk]

One solution ( of many, I'm sure ) should be something similar to this as a condition in the CF:

=AND(RIGHT(A2,1)="B",ISNA(MATCH(LEFT(A2,8)&"A",$A$2:$A$44,0)))

... adjust the column references to suit.

This sort of works, however it's highlighting all the 'A' values when a 'B' value is present....this should be the oppossite, i.e. ONLY 'B' values should be highlighted when 'A' values are NOT present, is this possible?

 

[GlennUK]

Describe what you did, over what range, what the active cell was when you did the CF, and any other relevant information. Show the formula that you are using for the condition in the CF. ( my formula should not highlight any 'A' values )

 

[Gerald Higgins]

GlennUK's formula works as requested, for me.

If A2 contains,
026E0065B
and the CF is applied to A2, A2 will NOT be highlighted if
026E0065A
IS found in A2:A44, and
WILL be highlighted if it is NOT found in that range.

 

[simi_uk]

Describe what you did, over what range, what the active cell was when you did the CF, and any other relevant information. Show the formula that you are using for the condition in the CF. ( my formula should not highlight any 'A' values )

I clicked on 'J' column header, the clicked the 'CF' menu item, changed the 1st condition to 'Formula Is' and inserted the formula you offered above making the changes advised, here's what i have (changes appear as bold:

=AND(RIGHT(A2,1)="B",ISNA(MATCH(LEFT(A2,8)&"A",$J$2:$J$15000,0)))

 

[GlennUK]

Select J2:J15000, and make the formula this:

=AND(RIGHT(J2,1)="B",ISNA(MATCH(LEFT(J2,8)&"A",$J$2:$J$15000,0)))

 

[Gerald Higgins]

Simi - what function does cell A2 perform in your setup ?
You might want to replace references to A2 with J2.