Referencing cells in multiple worksheets

[Andrew W]

I have a file with multiple worksheets (up to 50 in a file). The worksheets
have different names but contain data in the same locations and format as
each other.

What I want to do is create a table on a worksheet that copies a specific
cell from each worksheet that I can then sort.

Example:

4 worksheets called: Adam, Dave, Pete, Mike
I want to produce a table showing cell B2 from each of the above sheets to
look something like:

Sheet B2 Value
Dave 12
Pete 4
Mike 38
Adam 24

I would like to be able to do this without using a macro if possible. Can
anyone help???

Thanks

 

[Max]

With sheetnames listed in A2 down, eg: Dave, Adam, etc
cell refs entered in B1 across, eg: B2, K4, Z200, etc

Place in B2: =INDIRECT("'"&$A2&"'!"&B$1)
Copy across/fill down as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

 

[Andrew W]

Thanks Max. That was exactly what I needed.

With sheetnames listed in A2 down, eg: Dave, Adam, etc
cell refs entered in B1 across, eg: B2, K4, Z200, etc

Place in B2: =INDIRECT("'"&$A2&"'!"&B$1)
Copy across/fill down as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

 

[Max]

Welcome. Do press the "Yes" button below, pl.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik