OR Functions in a Conditional Formatting Furmula

K

Kevin Rodriguez

I'm trying to use the OR function in a CF formula without success. The
formula I'm using is:

=OR((LEFT(E87,2)="M-"),(VLOOKUP(E87,$Q$2:$Q$10,1,FALSE)>=1))

For some reason, the VLOOKUP is working, but the LEFT doesn't work. I
pulled out the Left into a cell and it works fine. But for some reason,
within the OR function in the CF it doesn't.

Any help is appreciated.

Kevin Rodriguez
 
F

Franz Verga

Kevin said:
I'm trying to use the OR function in a CF formula without success.
The formula I'm using is:

=OR((LEFT(E87,2)="M-"),(VLOOKUP(E87,$Q$2:$Q$10,1,FALSE)>=1))

For some reason, the VLOOKUP is working, but the LEFT doesn't work. I
pulled out the Left into a cell and it works fine. But for some
reason, within the OR function in the CF it doesn't.

Any help is appreciated.

Kevin Rodriguez
Click to expand...

Hi Kevin,

I think that without having a look to the file it's quite impossible to say
why the function didn't work...

Maybe there's some problem with your data table...

If you like you could upload an example to www.savefile.com or
www.rapidshare.de...


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
B

Biff

Hi!

You have some extra unnecessary ( ) but that's not the problem.

Your formula is "strange" so I'm guessing the problem is with the lookup.
=OR((LEFT(E87,2)="M-"),(VLOOKUP(E87,$Q$2:$Q$10,1,FALSE)>=1))
Click to expand...

First, you're testing for a TEXT value with LEFT but then in the LOOKUP
you're testing for >=1 which implies that E87 is a NUMERIC value.. A TEXT
value will evaluate to be >=1. I think you need to tell us what's in Q2:Q10
and what are the possible entries in E87.

Here's your formula wilthout the extra ( ):

=OR(LEFT(E87,2)="M-",VLOOKUP(E87,$Q$2:$Q$10,1,FALSE)>=1)

Biff
 
M

Max

Franz Verga said:
... If you like you could upload an example to www.savefile.com ..
Click to expand...

Believe savefile.com is down at the moment until ??

Some other free filehosts that could be used to upload samples:
http://www.flypicture.com/
http://cjoint.com/index.php

For cjoint.com (it's in French), just click "Browse" button, navigate to
your folder > select the sample file > Open, then click the button centred
in the page below (labelled "Creer le lien Cjoint") and it'll generate the
link. Then copy & paste the generated link as part and parcel of your
response here. The link will be good for 14 days.

Kindly note that no attachments should be posted *directly* to the newsgroup
 
M

Max

Not sure what happened, but perhaps just try this equivalent expression as
the cond format's formula (Condition 1):
=OR(LEFT(E87,2)="M-",ISNUMBER(MATCH(E87,$Q$2:$Q$10,0)))
 
Top