Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing cells in multiple worksheets
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing cells in multiple worksheets
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing cells in multiple worksheets
Thanks Max. That was exactly what I needed.
"Max" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing cells in multiple worksheets
Welcome. Do press the "Yes" button below, pl.
-- Max Singapore http://savefile.com/projects/236895 Downloads:16,700 Files:356 Subscribers:53 xdemechanik --- "Andrew W" wrote: Thanks Max. That was exactly what I needed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Referencing multiple worksheets | Excel Discussion (Misc queries) | |||
Referencing Cells on Different Worksheets | Excel Worksheet Functions | |||
Macro referencing multiple worksheets | Excel Discussion (Misc queries) | |||
Formula Referencing data on multiple worksheets | Excel Worksheet Functions | |||
Summary worksheet referencing multiple worksheets | Excel Worksheet Functions |