How to copy a range from one tab to another in Calc while preserving the links to the first tab

[tp]

I’ve created a spreadsheet, and while creating it I kept it all in one tab for ease of use.

I now need to make it easier to read so I’m separating out sections into different tabs.

The spreadsheet is a financial model so in this case the original tab had assumptions, income statement, balance sheet and cashflow all on the one big sheet. I’d now like to put the income statement, balance sheet and cashflow statements on separate tabs, but I’d like to preserve the link in the formulas to the assumptions in the first tab.

When I try and just cut and paste, the numbers look like they have copied successfully but the formulas refer to the destination sheet. Any modification causes errors all across the sheet. I can link to the original cells and hide those rows but that doesn’t seem very efficient. A cut and paste-link fails with errors.

Any thoughts on how I can do this? Thanks

[Pedro1]

You need to convert your formulas from relative reference to absolute reference.

Example:

If you have a formula in cell B3 which is =A1, copying this formula to any other place, sheet or spreadsheet will paste the formula as relative, i.e. it will get the value from the cell that is one column to the left and two rows above.

However if you convert the formula to absolute (by editing the formula, placing the cursor near A1 and pressing Shift+F4) it will be converted to =$A$1 (obviously you can also type the dollar sign(s) manually)

This formula always refers to cell A1 if you copy or move it to another cell. Similarly moving it anywhere on another sheet will show the formula as =Sheet1.$A$1

Note that first dollar locks the Column (in this case A) and the second locks the row (in this case Row 1). Pressing consecutively Shift+F4 while editing the formula will cycle through several combinations of locking column and/or row.

[Lupp]

Assuming “Sheet” is meant using the word “Tab” and also assuming that there might be a third sheet some time using the same copied formulae it will be purposive to also give the sheet reference by absolute addressing: $Sheet1.$A$1 , e.g.

[Pedro1]

@Lupp, good point!

[KeithSand]

“cut-and-paste” and “copy-and-paste” behave differently.

“copy-and-paste” is as described by Pedro: links to other cells are interpreted as relative unless you specify absolute by using $. So the cell C3 with “=A3B3" when copied to C4 has "=A4B4” which is what we want when generating new content from old.

However if you “cut-and-paste” the cell from C3 to C4 then it retains the links and so will still be “=A3*B3” even though the formula is using relative references. This is what we want when rearranging content. These behaviours are the same whether you cut/copy and paste within or between sheets.

So though you claimed to have “cut and pasted” I suspect you “copy and pasted” - what I’d do as a backup if I didn’t realise it made a difference.