Excel sheet name from a cell value

I have a workbook that includes several worksheets that are each named with a unique identifier.

One of the sheets is my summary page that references values form each of the sheets. Because it is a pain to write out the name of the sheet each time I copy a formula I would like to grap the name of the worksheet from a value.

for example something like this is in cell B3

  ='B2'!B3

so if B2 on my current sheet is 'cjs123' then the formula should go to the sheet called cjs123 and retrieve the value in cell B3.

How can I create this functionality?
lvmllcAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nutschConnect With a Mentor Commented:
Use the indirect formula, as in:
=INDIRECT(B2 & "!B3)

or, if you have spaces in your tab names, put single quotes surrounded by double quotes.
=INDIRECT("'" & B2 & "'!B3)
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
=indirect("'"&b2&"'!b3")
0
 
nutschCommented:
If you want to have the B3 dynamic depending on where you place the cell, you can use the address() function, as in:

=indirect("'"&b2&"'!" & address(row(),column()))

Thomas
0
 
lvmllcAuthor Commented:
Thomas, I think your first response second example was missing a " behind b3. otherwise both yours and ssaqibh got me on the right track.
0
All Courses

From novice to tech pro — start learning today.