How to copy a formula that references a series of sheets?

[Mark McInerney]

Hi All,

I have 4 sheets name A, B, C and D.

The D worksheet is a summary sheet that pulls in data from A, B and C.

e.g.

=A!A4
=A!C4


There are approx 50 of these references to Cell A. Is there an easy way to copy the same formulas but reference Sheet B and C without having to retype them all?

Thanks for any help - appreciated.

Mark.

 

[Comfy]

If you have the sheet names referenced in sheet D you can use the INDIRECT() function


Excel 2010

	A	B	C
1	A	B	C
2	15	20	25

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

D​

Worksheet Formulas Cell Formula A2 =SUM(INDIRECT(A1&"!A1:A5")) B2 =SUM(INDIRECT(B1&"!A1:A5")) C2 =SUM(INDIRECT(C1&"!A1:A5")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>