Hi!

What exactly are you trying to do?

You don't need to use a wildcard with SEARCH. SEARCH returns the number of
the character at which a specific character or text string is first found.

For example:

A1 = material......=SEARCH("mat",A1) = 1
A2 = raw material......=SEARCH("mat",A2) = 5
A3 = format......=SEARCH("mat",A3) = 4
A4 = style......=SEARCH("mat",A4) = #VALUE!

'anotherExcel.xls'!Sheet1'!$E$12

If you're referencing another file the correct syntax is:

[anotherExcel.xls]Sheet1!$E$12

When I said point to the references what I meant was use your mouse:

Start typing the formula:

=SEARCH("mat",

When you reach the point in the formula to enter the reference use your
mouse and navigate to the other file (have the other file open) Sheet1 and
select cell E12. This way Excel will automatically insert the path for you..

My guess is that you really don't want to use SEARCH but I need a better
explanation of EXACTLY what you're trying to do. Are you wanting to count
how many cells contain the substring "mat" ?

Biff

"gloss" wrote in message
...
Still having the same problem

search("mat*",'anotherExcel.xls'!Sheet1'!$E$12)

works but is inefficient for what I want and because I'll be applying
this
to multiple fields

search("mat*",'anotherExcel.xls'!Sheet1'!$E$1:$E$1 2) ...*

doesn't work, I initally tried the point method suggested and is exactly
the
same formula as above in ...* (except that I grouped my data)

I'm new to excel, only started today, and could have misunderstood by what
you meant by point to the reference (the easier method) could you please
elaborate

Thank you for your help



"Biff" wrote:

See reply in .Misc

Biff

"gloss" wrote in message
...
Hi

Does anyone have any idea how I can do a search and extract data from a
sparate worksheet

for example

search ("smi*", 'anotherWorksheet.xls'!Users)

does not recurse through the column in the second worksheet

what am i doing wrong?

Any help is much appreciated