software

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 amp; 3 -
in the formula)

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

Hope this helps - Giz

quot;tomsmithersquot; wrote:

gt; I would like to use a function to create a sumamry sheet from a number of
gt; worksheets in a work book.
gt;
gt; I would like to create a single worksheet with references to the same cell
gt; (i.e A3) but on different worksheets throughout the book.
gt;
gt; Therefore rather than keepng the worksheet absolute when autofilling, I
gt; would like to keep the cell range absolute (easy enough by using $) and
gt; instruct autofill to pickup a different worksheet for each cell it fills
gt; 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 quot;list of sheet namesquot; 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 amp; copy across as
many rows as you have sheets:

=INDIRECT(ADDRESS(3,1,1,,INDIRECT(quot;Aquot;amp;COLUMN()-2)))

The quot;-2quot; next to the column function is needed if the answers are to
start in column C (ie col C - 2 = col A or = 1) amp; 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 quot;-1quot; etc.
(adapted from www.ozgrid.com/Excel/excel_copy_across.htm)

hth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=505257