I have a spreadsheet with some formulas for processing my financial data. That uses a sheet produced by an application that I have minimal control over. So my sheet links into various cells on the data sheet.
The problem I have is if there are changes on the data sheet. If an investment is sold, or added, then the positions on this sheet will move around. When I used Excel to manage it, I could open the data sheet and mine, then if I made changes to the data sheet they would generally be reflected in the links. As an example, let's say I have a cell on my sheet linking to the data sheet like:
accounts.xls'#$Sheet1.E118
If I were to insert a blank row above that in the data sheet, the link in mine would automatically change to point to E119. Similarly, if I delete a row, it would link to E117. So I could manage changes by comparing the new and old data sheets to see where rows had changed, then make those changes as dummies in the old sheet. Then close everything and replace the data sheet.
That doesn't seem to work with Calc. If I add or delete a row in the data sheet, then links are pointing to the wrong data and I have to go and update the links to the new locations.
Is there any way handy to do what I'm looking for? Hopefully I 'splained this in a reasonably coherent manner.
Reply:
It sounds to me like you need to change the setting at Tools -> Options -> OpenOffice Calc -> General -> Input settings -> Expand references when new rows/columns are inserted