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

If you want the row reference to alter as you drag down, then you need
to take the absolute reference off the row identifier
=sheet1!b$4

=sheet1!b4


--
Regards

Roger Govier


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

Your question is not clear at all. You say data is drawn from different
sheets, but the formulae you quote all refer to Sheet1. By anchoring "B$4"
the row numbers, when you copy down, the row numbers will obviously remain
the same, hence the fact that all info comes from the same 3 cells.

What is it that you want to achieve here? If you want to use the same cell
addresses from several different sheets, then dragging down will not have any
effect on changing the sheet numbers. Depending on the number of sheets, you
will either have to change the sheet numbers manually, or use a macro to do
this.

"triffidbook" wrote:

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.

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.