variable substitution in a formula

[NHRunner]

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]

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]

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]

You sure?

Try copying both lines (it's a long formula) from that post.
Then select your cell and paste into the formula bar.

If that doesn't work, post what you tried.

I added/changed this portion of your formula:

indirect("'" & a1 & "'!$A$1:$A$1318")

The "'" surround the A1. A1 could hold a sheet name that needs to be surrounded
by apostrophes--like a number or a name with a space in it or a name that looks
like an address.

The stuff inside the () is just 3 strings that are concatenated.

 

[David Biddulph]

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

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

 

[NHRunner]

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

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?