Summary worksheet referencing multiple worksheets

Scenario: multiple worksheets (sheet1,sheet2,sheet3,etc) sheet 1 is a
summary. Sheet2!A1 contains the number 22, Sheet3!A1 contains the number 33

Based on the value in Sheet1!A1 I want to return a value in Sheet1!A2 based
on the data in the remaining sheets. In Sheet1!A1 I want to enter "Sheet2"
and then in Sheet1!A2 I want the value 22 returned from Sheet2!A1. Similarly
if I enter in Sheet1!A1 "Sheet3" and then in Sheet1!A2 the value 33 should be
returned which comes from Sheet3!A3.

I'm assuming you made a typo error with as you have said 33 comes from both Sheet3!A1 and Sheet3!A3. Assuming it is A1 in all the other sheets that you want to reference, paste this formula in Sheet1!A2

=INDIRECT(A1 & "!A1")

Cheers

In Sheet1
-------------
With the sheetname input into cell A1
(as per post)

Try in A2: =INDIRECT("'"&A1&"'!A1")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Jon" <[email protected]> wrote in message
news:[email protected]...
> Scenario: multiple worksheets (sheet1,sheet2,sheet3,etc) sheet 1 is a
> summary. Sheet2!A1 contains the number 22, Sheet3!A1 contains the number
33
>
> Based on the value in Sheet1!A1 I want to return a value in Sheet1!A2
based
> on the data in the remaining sheets. In Sheet1!A1 I want to enter "Sheet2"
> and then in Sheet1!A2 I want the value 22 returned from Sheet2!A1.
Similarly
> if I enter in Sheet1!A1 "Sheet3" and then in Sheet1!A2 the value 33 should
be
> returned which comes from Sheet3!A3.

Please note that Max's post is more correct as my post will not work for sheet names that are 2 words or more. oops!