Formula in a sheet reference

[Guest]

Is it possible to design a formula that would reference a particular sheet
based on the value in a cell. For example, I want my "master" sheet to pull
data from another sheet whose name is the same as the value of a cell on the
"master" (i.e. on the master, cell A5 contains the value 3 and I want the
other data in that row to come from a sheet named "3").

 

[Guest]

indirect()
=indirect(A5&"!B1")
would get the data in sheet 3 cell B1 if 3 is in A5

 

[Don Guillett]

Look in the help index for INDIRECT

For cell a4 in the sheet in cell a5
=INDIRECT(A5&"!"&"A4")

 

[KL]

just to make the formula a bit more error-proof, I would add the aphostrophes:

=INDIRECT("'"&A5&"'!A4")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[Guest]

Thank you so much--I would never have found the INDIRECT function in Help.
It works just like I want it to.

just to make the formula a bit more error-proof, I would add the aphostrophes:

=INDIRECT("'"&A5&"'!A4")

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

Look in the help index for INDIRECT

For cell a4 in the sheet in cell a5
=INDIRECT(A5&"!"&"A4")