difficulty with Search function that incorporates multiple workshe

need help urgent

Using 'Search' function with wildcard to find matching data in column of
another worksheet eg

search("mat*",'anotherExcel.xls'!Suppliers)

formula does not recurse through column

Is there a way i can overcome this or is there another formula or method I
could apply

Thanks

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("mat",[anotherExcel.xls]Sheet1!$A$1:$A$10))))

A return >0 means there's a match.

The best way to write these types of formulas is to open the other file and
point to the references. Let Excel put the path in for you.

Biff

"gloss" <[email protected]> wrote in message
news:[email protected]...
> need help urgent
>
> Using 'Search' function with wildcard to find matching data in column of
> another worksheet eg
>
> search("mat*",'anotherExcel.xls'!Suppliers)
>
> formula does not recurse through column
>
> Is there a way i can overcome this or is there another formula or method I
> could apply
>
> Thanks
>
>