Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Excel sheet name from a cell value

Posted on 2013-12-03
Medium Priority
674 Views
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?
0
Question by:lvmllc
• 2

LVL 39

Accepted Solution

nutsch earned 800 total points
ID: 39693514
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

LVL 43

Assisted Solution

Saqib Husain, Syed earned 400 total points
ID: 39693519
=indirect("'"&b2&"'!b3")
0

LVL 39

Expert Comment

ID: 39693523
If you want to have the B3 dynamic depending on where you place the cell, you can use the address() function, as in:

Thomas
0

Author Comment

ID: 39693547
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month11 days, 8 hours left to enroll