using worksheet references in formulas

[-sb]

I'm referencing data from one worksheet in a formula:

=SUM(IF('1'!F5:'1'!F16 = 6,IF('1'!G5:'1'!G16 ="YES", 1,0),0))

I'd like to reuse this formula for additional worksheets that will be
added over time without having to manually type in the worksheet
reference '1'!

Is there a way to substitute entries in a column for the hard coded
worksheet reference '1'! ?

Here's an example of what I'm trying to accomplish

Column A contains the references to all worksheets
Column B contains the results of the formula

A B
1 formula referencing sheet 1 derived from cell A1
2 formula referencing sheet 1 derived from cell A2
3 formula referencing sheet 1 derived from cell A3
4 formula referencing sheet 1 derived from cell A4


A second question I have: Is there a way to apply the worksheet
reference to the formula itself so that I don't have to apply it to
each cell in the range.

i.e. is there a way to achieve something along the line of this:

='1'!SUM(IF(F5:F16 = 6,IF(G5:G16 ="YES", 1,0),0))

thanks for your help with this.

 

[Bob Phillips]

=SUM(((INDIRECT("'"&A1&"'!F5:F16")=6)*(INDIRECT("'"&A1&"'!G5:G16")="YES")))

still an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[-sb]

Bob,

I have a little bit of learning to do regarding how to use the Indirect
function, but I did verify that it addresses my needs.

thanks. Much appreciated !

-sb