Alternatively try:

=INDIRECT(INDEX({"Sheet2","Sheet3","Sheet4","Sheet 5"},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.