Automatic update of links into data sheet

[Earl LG]

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 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.

[RusselB]

Welcome to the Forums.
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
I don't know what your setting is, but whatever it is, change it, then give your spreadsheet a try.
While the setting specifies "inserted" to the best of my knowledge the opposite occurs when rows/columns are deleted.

[Earl LG]

I'll take a look at it, thanks.

[Earl LG]

Unfortunately, that didn't seem to help.

[Earl LG]

Not an exact solution but copying the data sheet to a tab in the main sheet worked. Changes to the data tab would update where links pointed.