Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying and auto updating a formula
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 to calculate sheets B to I, and so on for the rest of the year, any ideas? i know its probably simple |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Updating | Excel Worksheet Functions | |||
auto updating list | Excel Worksheet Functions | |||
Cell formula not updating, auto calc on, over 100 sheets | Excel Discussion (Misc queries) | |||
Auto Updating Formula | Excel Discussion (Misc queries) | |||
How can I use the NOW function and keep it from auto updating? | Excel Worksheet Functions |