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