conditional formatting

[coltheplumb]

Hi All ok i have this in C/F in cell K4 =trim($f4)=”a” which turns cell K4 yellow when “a” is in f4 which works fine, how could i alter this so that it coves a bigger range, something like
=trim($f4, $H4)=”a,b,c” so will turn Cell K4 yellow only if an a,b or c appears in both cells F4 and H4. Or is this not possible.
All help appreciated
Colin X

 

[gsistek]

Try:
=AND(ISNUMBER(MATCH(TRIM($F4),{"a","b","c"},0)),ISNUMBER(MATCH(TRIM($H4),{"a","b","c"},0)))

 

[coltheplumb]

It looked wonderful but when i put it in I got this you may not use, intersections, or array constants for C/F criteria

 

[Marcelo Branco]

An alternative formula

=AND($F4<>"",ISNUMBER(SEARCH(TRIM($F4),"abc")),$H4<>"",ISNUMBER(SEARCH(TRIM($H4),"abc")))

M.

 

[coltheplumb]

No it doesn't like that either ;-(

 

[Marcelo Branco]

No it doesn't like that either ;-(

Well it worked for me...

But, re-thinking, i realized that the formula has some flaws.

The best you can do is:
1. Create a range with 3 cells containing
a
b
c

2. Name it, say, MyRange

3. Use the formula suggested above by gsistek replacing the array {"a";"b";"c"} by MyRange, i.e.,

=AND(ISNUMBER(MATCH(TRIM($F4),MyRange,0)),ISNUMBER(MATCH(TRIM($H4),MyRange,0)))

Hope this helps

M.

 

[coltheplumb]

No still not working for me, maybe cos im using 2010?

 

[Marcelo Branco]

No still not working for me, maybe cos im using 2010?

Works for me. Could you be more specific?

Please, take a look at
http://www.mrexcel.com/forum/board-announcements/685723-help-us-help-you.html#post3393381

M.