How do I have a formula lookup a sheetname?
 

I have a workbook with multiple worksheets.
Sheet 1: Has information needed to be retrieved and put into specific cells on
Sheet2.
Sheet 2: Col. A lists names of worksheets in workbook
In Col. B I want to write a formula that will lookup the
worksheet, using
the worksheet name in Col. A, then retrieve a value in a
specific cell on
that worksheet.

I don't want to "hardcode" the name of the worksheet in the formula (i.e.
'Sheet1'!H14), because that sheetname will always be changing.

You can use INDIRECT, assume A2 holds the sheet name

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


Regards,

Peo Sjoblom

"Mim" wrote:

I have a workbook with multiple worksheets.
Sheet 1: Has information needed to be retrieved and put into specific cells on
Sheet2.
Sheet 2: Col. A lists names of worksheets in workbook
In Col. B I want to write a formula that will lookup the
worksheet, using
the worksheet name in Col. A, then retrieve a value in a
specific cell on
that worksheet.

I don't want to "hardcode" the name of the worksheet in the formula (i.e.
'Sheet1'!H14), because that sheetname will always be changing.

Awesome! That was much easier than I thought it would be!!!!!
Thanks

"Peo Sjoblom" wrote:

You can use INDIRECT, assume A2 holds the sheet name

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


Regards,

Peo Sjoblom

"Mim" wrote:

I have a workbook with multiple worksheets.
Sheet 1: Has information needed to be retrieved and put into specific cells on
Sheet2.
Sheet 2: Col. A lists names of worksheets in workbook
In Col. B I want to write a formula that will lookup the
worksheet, using
the worksheet name in Col. A, then retrieve a value in a
specific cell on
that worksheet.

I don't want to "hardcode" the name of the worksheet in the formula (i.e.
'Sheet1'!H14), because that sheetname will always be changing.