using worksheet references in formulas

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.


--
-sb
------------------------------------------------------------------------
-sb's Profile: http://www.excelforum.com/member.php...o&userid=36861
View this thread: http://www.excelforum.com/showthread...hreadid=565731

=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" wrote in message
...

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.


--
-sb
------------------------------------------------------------------------
-sb's Profile:
http://www.excelforum.com/member.php...o&userid=36861
View this thread: http://www.excelforum.com/showthread...hreadid=565731

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


--
-sb
------------------------------------------------------------------------
-sb's Profile: http://www.excelforum.com/member.php...o&userid=36861
View this thread: http://www.excelforum.com/showthread...hreadid=565731