I have 50 sheets in one workbook and a summary sheet at the end. I want the
data from cells b4, c20 and c21 on each of the 50 sheets to appear on a
seperate row in the summary sheet. Therefore, I want the cell references in
the summary to remain static but the sheet number to increment by one on each
subsequent row. Sorry for the confusion, I hope this clarifies it.

"RagDyeR" wrote:

The last line in your OP throws me!

You're talking about sheets throughout your post, and then you come in with
*workbook*.

Assuming you really mean different sheets in the *same* WB, where all these
sheets have the XL default names, try these formulas in A1 and B1, and C1
respectively:

=INDIRECT("Sheet"&ROWS($1:1)&"!B4")
=INDIRECT("Sheet"&ROWS($1:1)&"!C21")
=INDIRECT("Sheet"&ROWS($1:1)&"!C20")

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"triffidbook" wrote in message
...
Can anyone advise me how to autofill formulas which will take into account
the data is from different sheets please?

I have several sheets I am wanting to pull data from as a summary sheet, but
at the moment I am having to manually type in the formula for each sheet in
the summary. I have tried the drag auto fill but it only copies the
existing
selection.

e.g. In cell A1, b1 and c1 respectivley I have entered =sheet1!b$4
=sheet1!c$21 =sheet1!c$20 and then correspondingly in the subsequent rows
below. The data is taken from the same cells in each workbook.