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.

You can use editreplace
or
look in the help index for INDIRECT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dsweeney" wrote in message
...
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.

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.

--
Rick (MVP - Excel)


"dsweeney" wrote in message
...
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.

works perfect thank you very much Rick!

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"dsweeney" wrote in message
...
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.