Look in the help index for SEARCH. Then, if you still need help.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveMoore" wrote in message
...
Why doesn't my SEARCH function work between spreadsheets?

I collect payroll data from a variety of different sources; names of
personnel are entered in a variety of ways, e.g. Mr David Moore, David
Moore, Dave Moore, D Moore, Moore D, - with and without periods, with
and without spaces. So trying to match up records is something of a
nightmare!

I have met this challenge by creating an additional column in my
worksheet/spreadsheet for the Surname only with the intention of using
the SEARCH function e.g. =SEARCH(O30,'[Payroll.xls]631131'!$B$8:$B
$160). You will see that this example takes the search string from
my working spreadsheet and searches a field in another (open)
spreadsheet but always returns "#VALUE!" even when I know there is a
match. The same instruction works if in the same worksheet but NOT
between worksheets in the same spreadsheet.

Is this a limitation of the function, or am I doing something wrong?

Appreciate any help.

Regards,
Dave Moore