difficulty with Search function that incorporates multiple workshe

G

gloss

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
 
B

Biff

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
 
Top