=INDIRECT("Sheet" & (ROW()) & "!A5")

Entered in A1 of new sheet.

Copy down to A31............will increment the Sheet number but not the
cellref.


Gord Dibben MS Excel MVP


On Tue, 16 Mar 2010 13:10:08 -0700, Mike Archer
wrote:

Hello. I have a workbook with 31 sheets of data. Each sheet is a form with
a record of data. I would like extract the data into a list. For example,
range A5 is the same field on each sheet, so I want to autofill a formula
reference the same cell on different sheets. I suspect I need a macro for
this, but wanted to make sure first. Can you autofill like this
=Sheet1!$A$5, =Sheet2!$A$5, =Sheet3!$A$5?