How do I create a formula that isn't made invalid when I delete a row.
For example in cell F12 I have the formula: =F11+D12-E12
This basically says take the value from the cell above then add the value of the cell 2 to the left and subtract the value in the cell directly to the left.
However, because I'm using actual cell addresses, as soon as I delete a row, all the rows below become invalid.
How do i express the formula by relative position (ie = "1 above" + "2 to left" - "1 to left")
Thanks.
You can use either
=OFFSET(F12,-1,0)+OFFSET(F12,0,-2)-OFFSET(F12,0,-1), or=INDIRECT("F11",true)+INDIRECT("D12",true)-INDIRECT("E12",true)=INDIRECT("R11C6",false)+INDIRECT("R12C4",false)-INDIRECT("R12C5",false)=INDIRECT("R[-1]",false)+INDIRECT("C[-2]",false)-INDIRECT("C[-1]",false)Both functions also allow to specify ranges, just use whatever has your personal preference (see Excel Help)…
INDIRECT function's first parameter, you can also use the ADDRESS function in various ways… :]
indirect: if offset(this,-1,0) is your goal, you can use indirect("R[-1]",false), or if you really need offset, then I suggest using offset(indirect("RC",false),-1,0).
this syntax work? I type =OFFSET(this,-1,0) into Excel and it complains about it. Offset would be great, but i need it to offset from the cell when i don't know the cell's address.this by indirect("RC",false) work? See my previous comment…=OFFSET(F12;-1;0) for above)
Reading between the lines... is your actual problem creating a running total from additions and deductions columns?
You can sum each column starting with an absolute reference and ending with a relative reference
Assuming row 11 is your first, put =SUM(D$11:D12)-SUM(E$11:E12) in F12.
As you copy this cell down the absolute reference remains the same where the relative reference automatically updates extending the range. You can even copy the formula up one line and it will work on the first line too.
Deleting and inserting lines doesn't break the formula, though any extra cells would be included in the SUM().
