Thank you Dave & David for your replies,

and yes it was "operator" error in my attempt to use Dave's first message.

I tried to hand key the formula instead of copying it, and I didn't notice
the right paren (which I thought was missing) because it was more to the
right than I thought it should be.

In fact, I still don't understand why the range of the COUNTIF is inside
the INDIRECT function. I thought all I needed was to redirect the reference
to the cell in row 1.

However, there are a number of formulas I've used that I know work but
don't know why. I'll study this one further.

This change you've helped me with is enormously helpful as it makes the
copying of the formula SO easy now.

Thanks again
Steve

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
The parentheses and quotes seem balanced as far as I can see, and Excel
(for me) is happy that it's valid.

Perhaps you've forgotten the second part of the formula, which wrapped
round to the next line in the message? [But that doesn't affect the parts
that you talked about.]

Did you copy and paste from the message, or did you attempt to retype?
--
David Biddulph

"NHRunner" wrote in message
...
The formula you suggested says it's invalid. It looks like it is missing
a right paren for the close of the indirect function, but if I add that
it is still invalid.

The single/double quotes you use in what appears to be the indirect
function seem unbalanced as well but then I'm not sure of what should be
quoted or not.

Steve

"Dave Peterson" wrote in message
...
I'd try:

=IF((COUNTIF(indirect("'" & a1 &
"'!$A$1:$A$1318"),"*"&B2&"*"&C2&"*")0),
"maybe", "no")

A1 contains the name of a worksheet in this workbook, right?

FYI: =indirect() won't work with external workbooks if that external
workbook
is closed.

NHRunner wrote:

I will using the following formula in hundreds of rows in about 30
columns
starting at column D
Row 1 in column D will have the names of 30 other sheets.

If D1 contained the string "groton" how would I change the following
formula
to use the string in row one.

=IF((COUNTIF(groton!$A$1:$A$1318,"*"&B2&"*"&C2&"*" )0),"maybe", "no")

Thanks
Steve

--

Dave Peterson