Referencing the name in a cell to change a formula?

[mikey66]

Column A contains sheet names.
Column B contains a formula that extracts data from a cell on that sheet and places it in the cell.

I need to write the formula so that the sheet name changes based on the name in column A.

For example:
A1 has the name Apple in it
B1 has the formula: =Apple!$A$1 which returns the value that is in A1 of the Apple sheet.
How do I write it so that when I drag that formula to B2, it will contain the sheet name referenced in A2? = ? !$A$1

Thanx

 

[Richard Schollar]

Hi

Use INDIRECT:

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