pull in cell text to referrence sheet name in formula

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.
 
R

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.
 
D

dsweeney

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top