How to use a cell value to reference a worksheet name

[S2]

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

 

[Biff]

Hi!

Try this:

A2 = Sheet1

=INDIRECT(A2&"!D4")

Evaluates to:

=Sheet1!D4

Biff

 

[S2]

Woo-hoo! That ROCKS Biff, thx!
S2

Hi!

Try this:

A2 = Sheet1

=INDIRECT(A2&"!D4")

Evaluates to:

=Sheet1!D4

Biff