[Dropped] Sheet merging

[neil-uk]

Hi Guys,

I have around 6 sheets in the same file, all with the same name headings etc on them.

For neatness, I was looking to combine all the info on the 6 sheets to 1 sheet.

Problem is that there's several formulas in each of the six, so copy and pasting will mean re-writing them all.

So I'm wondering if there's a simple merge option so that for example Sheet 1 'joins' onto the bottom of Sheet 2 for example?

Cheers
Neil

[acknak]

Did you try copy/paste?

Calc normally adjusts the moved formulas so that they refer to the same cells they did before the move.

If copy/paste doesn't manage it, you could try cut/paste as well; Calc treats the two operations differently in terms of the formula adjustments.

[squenson]

No simple way to do it, but if you select all your active cells in Sheet2, copy/paste them to Sheet1, the formulas will be automatically updated.

[Villeroy]

We can not know what you are writing about unless you show us the formulas in question and how they are copied/moved across sheets.

If you want to copy data from some sheet to the area below the used range of sheet #1 adjusting all the references to that used range on the fly:
1. Tools>Options>Calc>General: "Expand references ..." = ON
2. Copy used range from another sheet.
3. Select the first cell below the used range of sheet #1
4. Edit>Paste-Special with option "Shift Cells Down"

Any formula that refered to Sheet1.A1:F100 refers to Sheet1.A1:F150 after you inserted 50 rows below row 100.
=SUM(Sheet1.A1:A100) becomes =SUM(Sheet1.A1:A150)

Any references moved from other sheets to Sheet1 will refer to the very same cells as before.
Any references copied from other sheets to Sheet1 will refer to the same relative positions.