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.

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.

Hi

Well that is exactly what RD's suggestion should give you.

--
Regards

Roger Govier


"triffidbook" wrote in message
...
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.

Yeah, that's cracked it. Thanks everyone :)

"Roger Govier" wrote:

Hi

Well that is exactly what RD's suggestion should give you.

--
Regards

Roger Govier


"triffidbook" wrote in message
...
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.