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