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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=505257