pull in cell text to referrence sheet name in formula

[dsweeney]

I have a workbook that contains formulas with sheet references. I have
created a template sheet that I am using over and over. On this template
sheet I have a have a range of cells with these formulas for example:
='sheet1'!c5
='sheet1'!c6
='sheet1'!c7
Every time I make a copy of the template sheet then I have to edit each one
of these cells and change the 'sheet' name to reference to correct
corresponding sheet. How can I set these formulas so that they have a
variable for the sheet name that is updated when I enter the desired sheet
name in a cell and have the formulas automatically pull that name in and set
it as the sheet name? for example if I type 001-200 in cell B5 then the
formulas change to look like this:
='001-200'!c5
='001-200'!c6
='001-200'!c7
Thanks for the help.

 

[Don Guillett]

You can use edit>replace
or
look in the help index for INDIRECT

 

[Rick Rothstein]

Assuming after you put the first formula in, that you will want to be able
to copy it down, this should do what you want...

=INDIRECT("'"&$B$5&"'!C"&ROW(A5))

Note the A5... the 5 is because your first formula starts with a reference
to Row 5 via its C5 cell reference... the 5 does not have anything to do
with the 5 in B5. The column letter (the A in the A5) could be any valid
column letter.

 

[dsweeney]

works perfect thank you very much Rick!

Assuming after you put the first formula in, that you will want to be able
to copy it down, this should do what you want...

=INDIRECT("'"&$B$5&"'!C"&ROW(A5))

Note the A5... the 5 is because your first formula starts with a reference
to Row 5 via its C5 cell reference... the 5 does not have anything to do
with the 5 in B5. The column letter (the A in the A5) could be any valid
column letter.