Automatically changing Sheet reference

[Dendrinos2]

As we know in a typical formula =SUM(A1:A5) if you copy it across it automatically changes to =SUM(B1:B5) then =SUM(C1:C5)

I have a sheet reference with the same formula =SUM(Sheet1!A1:A5) but what I need it to do is the automatically change the sheet reference as I copy it across so it would look like this =SUM(Sheet1!$A$1:$A$5) next cell =SUM(Sheet2!$A$1:$A$5) next cell =SUM(Sheet3!$A$1:$A$5) next cell =SUM(Sheet4!$A$1:$A$5) and so on.

Is there anyway to do this or do I have to manually change the sheet reference each time?

 

[repairman615]

Hello,

try:

=SUM(INDIRECT("Sheet"&COLUMN(A1)&"!$A$1:$A$5"))