But of course... yes... the single quotes are necessary in case the sheet
name is not a single word. Thanks for catching that.

Maybe the OP mistyped the 5 instead of typing a 4. To the OP... if that is
what you did, then this is what Biff was thinking about...

=SUMPRODUCT(G1:G4,INDIRECT("'"&C1&"'!B1:B4"))

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
I think you need to include all those stupid quotes for the sheet name:

=G1*INDIRECT("'"&C1&"'!B1")+G2*INDIRECT("'"&C1&"'! B2")+G3*INDIRECT("'"&C1&"'!B3")+G4*INDIRECT("'"&C1 &"'!B5")

Too bad B5 is the odd cell out!

=SUMPRODUCT(G1:G3,INDIRECT("'"&C1&"'!B1:B3"))+G4*I NDIRECT("'"&C1&"'!B5")

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Assuming you have the sheet name in C1...

=G1*INDIRECT(C1&"!B1")+G2*INDIRECT(C1&"!B2")+G3*IN DIRECT(C1&"!B3")+G4*INDIRECT(C1&"!B5")

--
Rick (MVP - Excel)


"Alaska_Word_novice" wrote
in message ...
I have a formula in the form:
=G1*'Producer 1'!B1+G2*'Producer 1'!B2+G3*'Producer 1'!B3+G4*'Producer
1'!B5

I would like to be able to replace 'Producer 1'! with any other
worksheet
from a list in a reference cell. Thus by changing the reference cell I
could come up with:
=G1*'Producer 2'!B1+G2*'Producer 2'!B2+G3*'Producer 2'!B3+G4*'Producer
2'!B5

Is this possible and if so how? I am very confused. I have attempted
to
use the indirect function but have not had any success thus far.

Thanks,
Andrew