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
A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

</tbody>

D​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=SUM(INDIRECT(A1&"!A1:A5"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUM(INDIRECT(B1&"!A1:A5"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=SUM(INDIRECT(C1&"!A1:A5"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]