Thank everyone very much for suggestions
Eric

"Ragdyer" wrote:

You might care to try this, using XL 4.0 macros.

First a caveat:
This should be used in XL02 or later.
Earlier versions *will* crash when copying this type of formula to other
WBs.
Can be used in earlier versions, as long as copying is restricted to sheets
within
the existing WB.

So then ... say your link formula is in A1.
Start my creating a 'named' formula that will return the actual formula text
from A1;

Click in B1, then,
<Insert <Name <Define,
In the "Names In Workbook" box, enter a short name, say
form
for formula.
In the "Refers To" box, replace whatever's there with this formula:
=GET.CELL(6,A1)
Then <OK


What you have now is a 'relative' *named formula* that when entered in *any*
cell, will return the contents of the cell (text, data, formulas) from the
*previous* column.

So, with A1 containing your formula of:
=C:\boy\[Data.xls]sheet!A1

In B1 enter:
=form

To get the contents of A1.
You'll see the text of your formula displayed.

NOW, simply wrap that in another formula to return the "1" you're looking
for:

=--ISNUMBER(SEARCH("boy",form))

Don't forget, the
=form
formula *only* looks at the cell in the *preceding* column to display it's
contents.

If you would want it to return the cell *2* columns over, click in C1,
instead of B1, when you enter the get.cell formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Eric" wrote in message
...
Thank everyone for suggestions

Does anyone have any suggestions? if the text is within a formula, such
=C:\boy\[Data.xls]sheet!A1, does anyone know how to return 1 if the
formula
contains "boy", else return 0.
Thank for any suggestions
Eric