When autofilling I would like to change worksheets rather than cel

I would like to use a function to create a sumamry sheet from a number of
worksheets in a work book.

I would like to create a single worksheet with references to the same cell
(i.e A3) but on different worksheets throughout the book.

Therefore rather than keepng the worksheet absolute when autofilling, I
would like to keep the cell range absolute (easy enough by using $) and
instruct autofill to pickup a different worksheet for each cell it fills
accross.

There may be a better way but this could work:

Assume a list of all worksheet names in cells A1-A4 and you want your
answers in cells B1-B4. Enter the formula in cell B1 and copy down. Each cell
in col B will show the value by sheet for cell C7 (row and column - 7 & 3 -
in the formula)

Sheet 1 =INDIRECT(ADDRESS(7,3,,,A1))
Sheet 2
Sheet 3
Sheet 4

Hope this helps - Giz

"tomsmithers" wrote:

> I would like to use a function to create a sumamry sheet from a number of
> worksheets in a work book.
>
> I would like to create a single worksheet with references to the same cell
> (i.e A3) but on different worksheets throughout the book.
>
> Therefore rather than keepng the worksheet absolute when autofilling, I
> would like to keep the cell range absolute (easy enough by using $) and
> instruct autofill to pickup a different worksheet for each cell it fills
> accross.

hi Tom
This is similar to the solution posted by Gizmo but incorprates a way of copying your formula across the sheet ie answers in cells C1 to F3 rather than B1 to B4.

First create a list of the sheet names in cell A1 downwards on a sheet. There are many ways of quickly doing this if you are familiar with macros eg try Googling "list of sheet names" or the VBE help shows the following code:

Set newSheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
newSheet.Cells(i, 1).Value = Sheets(i).Name
Next i

Once you have the list enter the following in column C & copy across as many rows as you have sheets:

=INDIRECT(ADDRESS(3,1,1,,INDIRECT("A"&COLUMN()-2)))

The "-2" next to the column function is needed if the answers are to start in column C (ie col C - 2 = col A or = 1) & increments the reference down a row for each column your formula is pasted across. If you were to start in column B it would need to be "-1" etc.
(adapted from http://www.ozgrid.com/Excel/excel_copy_across.htm)

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...