How to summarise data in the same place in multiple worksheets?

[Peter Oz 67]

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.

[Greg Wilson]

Try:

=INDIRECT(CHOOSE(ROW() - OS1, "Sheet2", "Sheet3", "Sheet4", "Sheet5") & "!"
& CHOOSE(COLUMN() - OS2, "A1", "C2", "E3", "G4"))

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



"Peter Oz 67" wrote:

> 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.

[Greg Wilson]

Alternatively try:

=INDIRECT(INDEX({"Sheet2","Sheet3","Sheet4","Sheet5"},1, ROW() - 1) & "!"&
CHOOSE(COLUMN() - 1, "A1", "C2", "E3", "G4"))

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

"Greg Wilson" wrote:

> Try:
>
> =INDIRECT(CHOOSE(ROW() - OS1, "Sheet2", "Sheet3", "Sheet4", "Sheet5") & "!"
> & CHOOSE(COLUMN() - OS2, "A1", "C2", "E3", "G4"))
>
> 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
>
>
>
> "Peter Oz 67" wrote:
>
> > 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.

[John James]

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($A2&"!"&B$1)
4. Copy this formula to the other blank cells in the table

Cheers,

[Greg Wilson]

Note 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

"Greg Wilson" wrote:

> Alternatively try:
>
> =INDIRECT(INDEX({"Sheet2","Sheet3","Sheet4","Sheet5"},1, ROW() - 1) & "!"&
> CHOOSE(COLUMN() - 1, "A1", "C2", "E3", "G4"))
>
> 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
>
> "Greg Wilson" wrote:
>
> > Try:
> >
> > =INDIRECT(CHOOSE(ROW() - OS1, "Sheet2", "Sheet3", "Sheet4", "Sheet5") & "!"
> > & CHOOSE(COLUMN() - OS2, "A1", "C2", "E3", "G4"))
> >
> > 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
> >
> >
> >
> > "Peter Oz 67" wrote:
> >
> > > 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.