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

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

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

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