How to use a cell value to reference a worksheet name

G

Guest

Greetings all -
I have a workbook that consists of a summary sheet, and several worksheets
which contain monthly data. In the summary worksheet, I have the names of the
other worksheets in column A.

I want to use the value of a cell (ie: the worksheet name) in a formula to
reference the monthly sheet. Ex:

Summary sheet
A__________B___C__D__E
1 Sheetname Jan Feb Mar Apr
2 Sheet1
3 Sheet2
4 Sheet3

Assume the target data in Sheet1!D4 is the value 45.

Now:
In cell B2 of this Summary Sheet, I want to enter a formula that combines
the name of the worksheet in ColA with the referene to the target cell (D4),
and then resolves to the value of the cell in Sheet1D4, ie: 45.

I've tried a direct reference:
=A2!D4 - but that doesn't work - Excel prompts me to "Update Value" and
opens a file dialog box.

Tried various combinations of CONCATENATE, such as:
=&B2&'!'&D4 (and variations on that theme)
....but can't seem to make this approach work either.

I'm sure this is a simple syntax thing, but I'm beating my head against a
wall trying to guess what it is - any ideas would be appreciated!

Best,
S2
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

worksheet name variable external data cell 2
Reference the same cell based on tab name 1
Relative worksheet reference for multiple worksheets in a workbook 5
How to autofill the same cell reference across multiple worksheet. 1
Can I use a worksheet name in a formula? 4
Return worksheet name containing value found in 3D reference funct 8
Linking to other worksheets 3
Build a reference to another worksheet from cell values in currentsheet 3

Top