Referencing cells in multiple worksheets

A

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
 
M

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
 
A

Andrew W

Thanks Max. That was exactly what I needed.

Max said:
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
Click to expand...
 

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

VBA Excel - Select a value from one cell in Excel to update a SQL table 1
How do I lookup data from multiple worksheets? 2
count cells with multiple criteria and two rows 2
Copy Cells from multiple Worksheets in same workbook. 2
Linking worksheets in the same workbook by names not cells 1
Cell reference between worksheets 4
One pivot table for multiple worksheets/tabs 2
formulae in excel worksheets 4

Top