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