pull in cell text to referrence sheet name in formula

  • Thread starter Thread starter dsweeney
  • Start date Start date
D

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.
 
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.
 
works perfect thank you very much Rick!

Rick Rothstein said:
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.
Click to expand...
 
Back
Top