On Thu, 14 Feb 2008 04:23:36 -0800 (PST), Sean wrote:

I have the following formula in Range A8:A30-

=IF('Mix Check'!U8="","",'Mix Check'!U8&'Mix Check'!N8&'Mix Check'!
O8&'Mix Check'!P8&'Mix Check'!Q8&'Mix Check'!R8&'Mix Check'!S8&'Mix
Check'!T8)

This will produce a result something like: - '1 Day Sales Mix missing
Mon; Tue' etc etc

How can I check in AA1; if my value that is held within A1 (this will
show a value of one of Mon, Tue, Wed, Thur, Fri, Sat, Sun), appears
anywhere within the result in A8; A11;A14, If it does appear, return
0, if it doesn't Return 0?

I assume the above is a typo, otherwise you could just enter

A8: 0


I have different non-contiguous range I wish to check hence A8; A11;
A14 etc

Thanks

To check AA1, and return 1 if present and 0 if not present:

=--OR(NOT(ISERR(SEARCH({"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat"},AA1))))

To check a different cell, change the cell reference.

To check to see if this value might be present in any of a series of
non-contiguous (or contiguous) cells, one method, short of using VBA, would be
to concatenate replace the within_text argument with a concatenation of the
range:

=--OR(NOT(ISERR(SEARCH({"Sun","Mon","Tue","Wed","Thu" ,"Fri","Sat"},
CONCATENATE(AA1,AA3,AA5,W2)))))

If you have more than 29 cells, then, instead of using the CONCATENATE
worksheet function, you will have to use the "&" operator, or a combination of
the two.
--ron