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

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