Copying a formula that contins a range of adjacent cells

[frappier]

Hi, I am trying to copy a formula which contains adjacent cells. The problem is when I copy the formula, Excel automatically adjusts the referenced cells in the formula depending on the direction in which I copy the formula (rows or columns). For example I have the formula =A1-SUM(C2:C5). When I copy this formula across (row) it's fine as it references the same range in the next column, but, if I copy it down (column) it changes to =A1-SUM(C3:C6). What I need is for it move to the following cells. For example, this formula in cell F5 =A1-SUM(C2:C5) when copied to F6 should read =A2-SUM(C6:C9) (the next block of cells) and if copied to F7 should read =A3-SUM(C10:13) and so on.

Is this possible to do either copying in rows or columns?

Thanks in advance.

Marc

[JBeaucaire]

In F5, then copy down or to the right:

=A1 - SUM(OFFSET($C$2, ROW(A1)*4-4, COLUMN(A1)-1, 4, 1))

[frappier]

Thanks for the quick reply. I will try this out. It will save me a lot of work!!

[JBeaucaire]

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.