Multi worksheet data look up

I am looking for a way to copy data from multi worksheets
to another worksheets.

I have 50 worksheets of data and what to copy an area of data
from any 1 of these 50 worksheets by typing in the sheet name
in a cell on the destination sheet.

In short I need a formula of some kind to find the
worksheet then the data.

"Ray" wrote...
I am looking for a way to copy data from multi worksheets
to another worksheets.

I have 50 worksheets of data and what to copy an area of data
from any 1 of these 50 worksheets by typing in the sheet name
in a cell on the destination sheet.

In short I need a formula of some kind to find the
worksheet then the data.

Let's say the worksheets were named AL to WY (2 letter US state abreviations).
If you want cell X99 from the worksheet given in the current worksheet's A1
cell, try

=INDIRECT("'"&A1&"'!X99")

If you want to copy and paste this formula with effectively relative cell
addressing, try

=INDIRECT("'"&$A$1&"'!"&CELL("Address",X99))

Even if you enter this formula in cell X99, it won't trigger circular recalc.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.