Take a look at INDIRECT() in XL Help:

=INDIRECT("'" & B9 & "'F52")

In article ,
"GMJT" wrote:

Is it possible for the contents of any given cell of the current worksheet to
be intrepretted as or converted to a worksheet (tab) name to be used to
display data in the indicated referenced cell?

EXAMPLE FORMULA : =UnknownFunction(B9)!$F$52 whereby the contents of
cell B9 of the current worksheet contains the Worksheet name from which the
contents of F52 of the referenced Worksheet is displayed.

RATIONALE : The above indicated formula could be in a locked cell of a
protected Worksheet while column "B" could be in an unlocked cell of the same
protected worksheet. This would allow non-permissioned users to see contents
of referenced cell (i.e., summary cell) from any given "protected" worksheet
simply by entering the name of a Worksheet (i.e., Tab label) from the current
Workbook.