ExcelBanter - Copying and auto updating a formula

On Feb 14, 9:51 am, Blade370
wrote:
I have a column AB (accum hours) on sheet I and it calculates a sum for
the previous 8 weeks (ie sheets A to H) which works fine, but when we copy
the formula on to the next week (sheet J) it stays the same when we need it
tocalculate sheetsB to I, and so on for the rest of the year, any ideas?
i know its probably simple

This solution may not be very elegant but it does work.

First, instead of naming your worksheets with letters, number them
sequentially and in the same cell on each worksheet, let us say AC1,
manually enter the sheet's number.

Assuming data is in column AA on all sheets, and starting with sheet 9
(your letter I), you want sums of the previous eight sheets to appear
in column AB, enter a formula on the following model in the cells in
that column where you want to see the totals.

=SUM((INDIRECT("'"&($AC$1-1)&"'!AA1")),(INDIRECT("'"&($AC$1-2)&"'!
AA1")))

The version here will, of course, only give you the sums of AA1 from
the two preceding pages. To go back farther, simply insert more terms
with higher values after the minus sign.

Once you have this formula set up in all the appropriate cells on
sheet 9, you may copy it to sheets 10 and beyond. On each sheet, it
will pick up the manually entered value in that sheet's AC1 to
identify the earlier sheets to be summed.

Allan Rogg