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" <sb.2bmapg_1154026207.0356@excelforum-nospam.com> wrote in message
news:sb.2bmapg_1154026207.0356@excelforum-nospam.com...
>
> 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
>

[-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