Copy text from same cell on every sheet to title sheet?

[Jon]

Hi,

hope someone can help on this.

What I have is a document which contains 101 separate worksheets. The
first sheet is a summary page and there are 100 other sheets which are
identical.

What I need is for the same cell on each of the 100 sheets to be copied
into the summary sheet, but moving down 1 row each time.

EG:
I want Cell A1 from sheet 1 to appear in cell A1 on the summary page.
I want cell A1 from sheet 2 to appear in cell B1 on the summary page.
I want cell A1 from sheet 3 to appear in cell C1 on the summary page etc

Can excel do this automatically?

 

[Max]

One way

Assuming the 100 sheets are named: Sheet1, Sheet2 ... Sheet100

In "Summary"
------------
Put in A1:

=OFFSET(INDIRECT("Sheet"&COLUMNS($A$1:A1)&"!$A$1"),ROWS($A$1:A1)-1,)

Copy A1 across to Cv1, fill down by as many rows as required

 

[Earl Kiosterud]

Jon,

Put this in A1 of the summary sheet, and fill down:
=INDIRECT("'Sheet"&B1 & "'!" & "A1")

In Column B, put 1, 3, 3, going down. Do this by putting 1 in the first
cell, then use the Fill Handle downward holding Ctrl.

Hide Column B.