Formula in a sheet reference
 

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").

Formula in a sheet reference
 

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

"KimC" wrote:

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").

Formula in a sheet reference
 

Look in the help index for INDIRECT

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

--
Don Guillett
SalesAid Software

"KimC" wrote in message
...
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").

Formula in a sheet reference
 

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/pr...A-9E6C73C09A36


"Don Guillett" wrote in message ...
Look in the help index for INDIRECT

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

--
Don Guillett
SalesAid Software

"KimC" wrote in message
...
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").

Formula in a sheet reference
 

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

"KL" wrote:

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/pr...A-9E6C73C09A36


"Don Guillett" wrote in message ...
Look in the help index for INDIRECT

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

--
Don Guillett
SalesAid Software

"KimC" wrote in message
...
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").