Autofill Formula

[Guest]

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.

 

[Roger Govier]

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

 

[Guest]

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.

 

[RagDyeR]

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!
=====================================================


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.

 

[Guest]

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.

 

[Roger Govier]

Hi

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

 

[Guest]

Yeah, that's cracked it. Thanks everyone

Hi

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