OO.org Calc: Copying formulae

Things are ticking along nicely. I’m currently not programming very much outside work – the desire to do so comes and goes. I’m still fiddling with computers though.

I was trying to make a spreadsheet with a few formulas in. When I copied them into each cell in a column Calc “helpfully” updated the cell references. Some of the cells I was refering to were fixed i.e. I wanted all the the cells in the column to refer to the same cell. I found a page explaining how to fool the software so none of the references in my formula were updated but I wanted some of them to be. Simple – but not obvious if you don’t know how

The concept I was missing (or had forgotten – it has been ages since I used Excel but presumably it works the same way there) was absolute and relative addresses, (which you can look up in Calc’s help). Basically a $A$0 will always refer to A0, $A0 will always refer to the A column but the row will be relative to the cell it is pasted into and A0 is entirely relative. (A much more complicated way of doing it would be to use the ADDRESS function).