|
pull in cell text to referrence sheet name in formula
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. |
pull in cell text to referrence sheet name in formula
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. |
pull in cell text to referrence sheet name in formula
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. |
| All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com