How can I reference a cell within a formula

[Alaska_Word_novice]

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

 

[Rick Rothstein]

Assuming you have the sheet name in C1...

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

 

[T. Valko]

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*INDIRECT("'"&C1&"'!B5")

 

[Rick Rothstein]

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"))