Can formulas referring to another sheet be copied to another file with relative addressing?

[jcarls]

I have a fairly large (2 meg) spreadsheet that is having severe corruption problems when I try to save it, and once that starts, LibreOffice usually crashes when I try to open it (and cannot “recover” it). I want to take one row of formulas from an earlier, working version and save them to a new sheet. Unfortunately, the existing formulas refer to another sheet ("$MasterList") in the same file.

Although I created a sheet called “MasterList” in a new, blank file, when I attempt to copy the formulas to another sheet in that new file, the copied formula still points to the old file. If I remove the “$” from the formulas, the original formula still works, but the copied version produces a #REF error.

Although I can turn all the formulas into plain text formulas before copying them, it would be easier if there was something in between, which copies the formula but recognizes that a “MasterList” sheet exists in the new file and does not try to link to the old file. Is this possible? Thanks!

[erAck]

You can get that working if the references use relative sheet-addressing (MasterList without $) and in the target document the to be referenced sheet has the same sheet-relative distance to the sheet position where you copy the formula to. For example, if your original document has sheets Foo,Bar,MasterList in that order and you copy formulas from sheet Foo, the target document should have Sheet1,Sheet2,MasterList if you copy formulas to Sheet1. If the original order was MasterList,Foo,Bar and you copy from sheet Foo then the target document should have MasterList,Sheet1. The actual sheet names and total number of sheets don’t matter, the relative sheet position does.

[King_ZZ]

I was using OpenOffice where I can copy a selection of formulas from a multiple sheet calc file to another multisheet calc file and it will copy all the formulas correctly, also doesn’t matter if the $ is locking sheets it will copy them the same.

I am having the same problem as this person with libreoffice calc

My other sheets are coded to the sheet I am replacing so if I copy sheet for ods1 to ods2, ods2s sheets that correspond to the sheet I have added will not recognise the new sheet

[King_ZZ]

Example ods1 & 2 are identical apart from different products

ods1 sheet1,2,3,4,5 ods3, sheet1

ods2 sheet1,2,3,4,5

If i have formulas on ods1sheet1,2,3 corresponding to ods1sheet1,2,3,4,5 and ods1sheet4,5 correspond to ods1,sheet2,3.

I currently have all sheets like this $sheet1.$A2

If i change the formulas & copy & paste contents of a sheet from ods1 to ods2 to within the same sheet it copies like this. $‘file location.ods1’#sheet1.$A2 other sheets on ods2 keep pointing at correct cells

[King_ZZ]

Am i using the $ correctly, should i be locking the sheets. If i do not lock the sheets what can happen?

I have about 15 ods files with 13 sheets all identical, apart from all of the products are different. At the moment I am altering formulas and changing some columns, so I have to copy these changes from the sheet and paste it to all of the other documents on the same sheet.

I am using e.g. $sheet1.A1 because I thought if I moved a sheet or added a sheet all my formulas will stay connected