How can I set the worksheet I am referencing in a formula to be the text in a
cell? I have many worksheets with the same formatted data. If I am able to do
this I can pull the data I need from each worksheet without retyping my
formulas by just referencing a row of worksheet names.
Microsoft Excel 2003 Professional

Try:

=INDIRECT("'"&$A$1&"'!B2") where A1 contains sheet name

"turn?around" wrote:

How can I set the worksheet I am referencing in a formula to be the text in a
cell? I have many worksheets with the same formatted data. If I am able to do
this I can pull the data I need from each worksheet without retyping my
formulas by just referencing a row of worksheet names.
Microsoft Excel 2003 Professional

Try the INDIRECT formula.

=INDIRECT("'"&A1&"'!B2")

This will return the cell value of B2 from the sheet that is named in cell
A1. So let's assume A1 contains the text value DATA. This formula returns
the value found in DATA!B2.

HTH,
Elkar

"turn?around" wrote:

How can I set the worksheet I am referencing in a formula to be the text in a
cell? I have many worksheets with the same formatted data. If I am able to do
this I can pull the data I need from each worksheet without retyping my
formulas by just referencing a row of worksheet names.
Microsoft Excel 2003 Professional

This did allow the reference to that cell. However when I pulled it across to
reference the different sheets it kept looking at the initial cell mentioned,
defeating the point of having the worksheet names listed.

"Elkar" wrote:

Try the INDIRECT formula.

=INDIRECT("'"&A1&"'!B2")

This will return the cell value of B2 from the sheet that is named in cell
A1. So let's assume A1 contains the text value DATA. This formula returns
the value found in DATA!B2.

HTH,
Elkar

"turn?around" wrote:

How can I set the worksheet I am referencing in a formula to be the text in a
cell? I have many worksheets with the same formatted data. If I am able to do
this I can pull the data I need from each worksheet without retyping my
formulas by just referencing a row of worksheet names.
Microsoft Excel 2003 Professional

So, the B2 portion of the formula is what you want to increment when copied?
Then perhaps, something like this will work:

=INDIRECT("'"&A1&"'!B"&ROW())

This will now use the row number that the formula is located in, to
determine which row to look in on the referenced sheet. You can add or
subtract from this also if needed. ROW()+1 or ROW()-1

Does that help?

"turn?around" wrote:

This did allow the reference to that cell. However when I pulled it across to
reference the different sheets it kept looking at the initial cell mentioned,
defeating the point of having the worksheet names listed.

"Elkar" wrote:

Try the INDIRECT formula.

=INDIRECT("'"&A1&"'!B2")

This will return the cell value of B2 from the sheet that is named in cell
A1. So let's assume A1 contains the text value DATA. This formula returns
the value found in DATA!B2.

HTH,
Elkar

"turn?around" wrote:

How can I set the worksheet I am referencing in a formula to be the text in a
cell? I have many worksheets with the same formatted data. If I am able to do
this I can pull the data I need from each worksheet without retyping my
formulas by just referencing a row of worksheet names.
Microsoft Excel 2003 Professional