isnumber query

[fry]

Hi All

Can someone tell me how to have 2 options using isnumber.

I'm using ISNUMBER(SEARCH("SUBMITTED",N156)) but I'd like to have "SUPPLY" & "SUBMITTED"

Is this possible??

Thanks

 

[Richard Schollar]

Hi

Perhaps:

=OR(ISNUMBER(SEARCH({"SUPPLY","SUBMITTED"},N156)))

 

[barry houdini]

You could also use COUNT, i.e.

=COUNT(SEARCH({"SUPPLY","SUBMITTED"},N156))>0

 

[Aladin Akyurek]

Hi All

Can someone tell me how to have 2 options using isnumber.

I'm using ISNUMBER(SEARCH("SUBMITTED",N156)) but I'd like to have "SUPPLY" & "SUBMITTED"

Is this possible??

Thanks

ISNUMBER(MATCH(N156,{"SUPPLY","SUBMITTED"},0))+0

 

[fry]

Hi Richard

Thanks for that but I should have mentioned that I'm using the function for conditional formatting and when I enter your suggestion excel tells me it's not allowed?????????

 

[fry]

Richard.....I should have added that the error message is "You may not use unions, intersections or array constants for conditional formatting criteria"

 

[fry]

Aladin

I get the same error

 

[fry]

Hi Barry

I also get the same error with yours...

My fault guys I should have mentioned the conditional formatting bit...

Maybe it's a bit too complicated for formatting

 

[Richard Schollar]

You could just separate out the search terms ie:

=OR(ISNUMBER(SEARCH("SUPPLY",N156)),ISNUMBER(SEARCH("SUBMITTED",N156)))

and use this, but I quite like the idea of making a named formula and using that in CF eg:

Insert>Name>Define

Refers to box:

=OR(ISNUMBER(SEARCH({"SUPPLY","SUBMITTED"},INDIRECT("RC",False))))

and call it myFormula

then in CF use Formula Is and:

=myFormula

Or select a worksheet range eg Z1:Z2 and write your search terms in there (Z1=SUBMITTED,Z2=SUPPLY) and then reference that in the CF (ie $Z$1:$Z$2 instead of {"SUPPLY","SUBMITTED"}

 

[fry]

Thanks a lot Richard, it works a treat, and thanks to everyone for replying, it is much appreciated!!