software

I have a large workbook with 50 or so worksheets that have the same layout.
I would like to create a summary table of data in the worksheets in a table
on a new worksheet. My aim is that each row in the table will be one of the
worksheets and each column will be a specific cell in the worksheet.
I can do this manually by copying formulae with absolute references to one
of the sheets, and then changing the name of the sheet in the formula, but
this would be quite laborious.
Does anyone know a quick way to do this, through Excel functions? I do not
know how to do macros or Visual Basic.

Try:

=INDIRECT(CHOOSE(ROW() - OS1, quot;Sheet2quot;, quot;Sheet3quot;, quot;Sheet4quot;, quot;Sheet5quot;) amp; quot;!quot;
amp; CHOOSE(COLUMN() - OS2, quot;A1quot;, quot;C2quot;, quot;E3quot;, quot;G4quot;))

Where OS1 is the row offset and OS2 is the column offset. These would be the
row number minus 1 of the top-left cell of the summary range and OS2 would be
the column number -1. So if the first cell containing the formula was B3 then
OS1 would equal 2 and OS2 would equal 1.

Note that you can list any sheets that you like and the cells in the source
sheets can be noncontiguous. Change sheet names and cell references to suit.

Regards,
Greg
quot;Peter Oz 67quot; wrote:

gt; I have a large workbook with 50 or so worksheets that have the same layout.
gt; I would like to create a summary table of data in the worksheets in a table
gt; on a new worksheet. My aim is that each row in the table will be one of the
gt; worksheets and each column will be a specific cell in the worksheet.
gt; I can do this manually by copying formulae with absolute references to one
gt; of the sheets, and then changing the name of the sheet in the formula, but
gt; this would be quite laborious.
gt; Does anyone know a quick way to do this, through Excel functions? I do not
gt; know how to do macros or Visual Basic.

Alternatively try:

=INDIRECT(INDEX({quot;Sheet2quot;,quot;Sheet3quot;,quot;Sheet4quot;,quot;Sheet 5quot;},1, ROW() - 1) amp; quot;!quot;amp;
CHOOSE(COLUMN() - 1, quot;A1quot;, quot;C2quot;, quot;E3quot;, quot;G4quot;))

This formula has a higher capacity for worksheets. Add the worksheet names
to the array inside the curly brackets (in double quotes separated by
commas). The first formula won't handle 50 worksheets without reconfiguring
it.

Regards,
Greg

quot;Greg Wilsonquot; wrote:

gt; Try:
gt;
gt; =INDIRECT(CHOOSE(ROW() - OS1, quot;Sheet2quot;, quot;Sheet3quot;, quot;Sheet4quot;, quot;Sheet5quot;) amp; quot;!quot;
gt; amp; CHOOSE(COLUMN() - OS2, quot;A1quot;, quot;C2quot;, quot;E3quot;, quot;G4quot;))
gt;
gt; Where OS1 is the row offset and OS2 is the column offset. These would be the
gt; row number minus 1 of the top-left cell of the summary range and OS2 would be
gt; the column number -1. So if the first cell containing the formula was B3 then
gt; OS1 would equal 2 and OS2 would equal 1.
gt;
gt; Note that you can list any sheets that you like and the cells in the source
gt; sheets can be noncontiguous. Change sheet names and cell references to suit.
gt;
gt; Regards,
gt; Greg
gt;
gt;
gt;
gt; quot;Peter Oz 67quot; wrote:
gt;
gt; gt; I have a large workbook with 50 or so worksheets that have the same layout.
gt; gt; I would like to create a summary table of data in the worksheets in a table
gt; gt; on a new worksheet. My aim is that each row in the table will be one of the
gt; gt; worksheets and each column will be a specific cell in the worksheet.
gt; gt; I can do this manually by copying formulae with absolute references to one
gt; gt; of the sheets, and then changing the name of the sheet in the formula, but
gt; gt; this would be quite laborious.
gt; gt; Does anyone know a quick way to do this, through Excel functions? I do not
gt; gt; know how to do macros or Visual Basic.


Hi Peter Oz 67,

On your summary sheet:
1. Put your source sheetnames in cell A2 and in the cells below it
e.g. Sheet1
2. Put your source cell references in cell B1 and in the cells to its
right e.g. B32
3. In cell B2 enter:
=INDIRECT($A2amp;quot;!quot;amp;B$1)
4. Copy this formula to the other blank cells in the table

Cheers,--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531276Note that the offsets were hard coded in the formula. Here, it is assumed
that the summary table starts in cell B2 and therefore the offsets are both 1.

Greg

quot;Greg Wilsonquot; wrote:

gt; Alternatively try:
gt;
gt; =INDIRECT(INDEX({quot;Sheet2quot;,quot;Sheet3quot;,quot;Sheet4quot;,quot;Sheet 5quot;},1, ROW() - 1) amp; quot;!quot;amp;
gt; CHOOSE(COLUMN() - 1, quot;A1quot;, quot;C2quot;, quot;E3quot;, quot;G4quot;))
gt;
gt; This formula has a higher capacity for worksheets. Add the worksheet names
gt; to the array inside the curly brackets (in double quotes separated by
gt; commas). The first formula won't handle 50 worksheets without reconfiguring
gt; it.
gt;
gt; Regards,
gt; Greg
gt;
gt; quot;Greg Wilsonquot; wrote:
gt;
gt; gt; Try:
gt; gt;
gt; gt; =INDIRECT(CHOOSE(ROW() - OS1, quot;Sheet2quot;, quot;Sheet3quot;, quot;Sheet4quot;, quot;Sheet5quot;) amp; quot;!quot;
gt; gt; amp; CHOOSE(COLUMN() - OS2, quot;A1quot;, quot;C2quot;, quot;E3quot;, quot;G4quot;))
gt; gt;
gt; gt; Where OS1 is the row offset and OS2 is the column offset. These would be the
gt; gt; row number minus 1 of the top-left cell of the summary range and OS2 would be
gt; gt; the column number -1. So if the first cell containing the formula was B3 then
gt; gt; OS1 would equal 2 and OS2 would equal 1.
gt; gt;
gt; gt; Note that you can list any sheets that you like and the cells in the source
gt; gt; sheets can be noncontiguous. Change sheet names and cell references to suit.
gt; gt;
gt; gt; Regards,
gt; gt; Greg
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Peter Oz 67quot; wrote:
gt; gt;
gt; gt; gt; I have a large workbook with 50 or so worksheets that have the same layout.
gt; gt; gt; I would like to create a summary table of data in the worksheets in a table
gt; gt; gt; on a new worksheet. My aim is that each row in the table will be one of the
gt; gt; gt; worksheets and each column will be a specific cell in the worksheet.
gt; gt; gt; I can do this manually by copying formulae with absolute references to one
gt; gt; gt; of the sheets, and then changing the name of the sheet in the formula, but
gt; gt; gt; this would be quite laborious.
gt; gt; gt; Does anyone know a quick way to do this, through Excel functions? I do not
gt; gt; gt; know how to do macros or Visual Basic.