View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default difficulty with Search function that incorporates multiple workshe

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" wrote in message
...
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