To simplify the problem, let's say I have an Excel sheet which has different numeric values in the cells A1 to B100 (two columns by 100 rows). In column C, I would like to have a formula that will multiply the numbers in A and B for that same row.

The old way I used to write the formula was buy entering in C1 `=A1*B1`

and then copy the formula down to C100. This worked great but isn't very robust because if someone moves around cells in columns A or B then the formula's will track the new location and mess things up.

Someone then showed me a trick so that this formula could be more robust. I would need to enter in C1 the formula `=A:A*B:B`

this effectively looks at the value in the same row and thus will continue to work as expected when the values in columns A and B are moved around.

Let's assume I now want to create a new calculated value in column D (e.g. value in A for same row + value in column A in previous row) such that the formula in D1 is `=A1`

. The formula in D2 would be `=A1+A2`

and this formula would then be copied down in column D. This is not as robust, is there a more robust way to do this so that it is immune from the moving around of the source cells (like my first example).

Many thanks in advance