[Solved] Calc won't copy sheets properly

[evilpilch]

Hi,

My apologies if this has already been covered. My issue is this: I am attempting to duplicate an entire sheet so I don't have to rebuild it from scratch. Most of the values I want to stay identical and I just want to change the odd one here and there. I thought the best way to do this would be to use the move/copy function and copy the sheet to a new location, however when I try this, the formulae all get screwed up and seem to refer to a cell 1 row down or 1 row across from the one I want (and the one that's in the sheet I copied from).

Now I'd have thought that the copy function should create a precise copy. But it seems not to. No matter if I use the copy sheet function, the copy / paste function or the copy / paste special function I get the same results - all my formulae are screwed up and the copied sheet refers to cells bumped down by one. Meaning I have to go through and copy & paste the correct formula CELL BY CELL! Which is laborious to say the least.

Is this a known problem with Calc? Has anyone else experienced this? Or is it just me?

Please help!

[crusader]

the formulae all get screwed up and seem to refer to a cell 1 row down or 1 row across from the one I want

Consider using Absolute References in your formulas...

[kingfisher]

That shouldn't be necessary. I copy a sheet without any changes to formulas using relative references.

Why would one row be added to formula references? The new sheet should be an exact copy of the existing sheet.

I have no idea why it should make a difference but the standard suggestion for a conundrum like this is to rename your settings folder (after closing all OO processes (including the quick starter) and restarting the software which will create a fresh profile. If that doesn't work, you can reinstate your old settings folder (aka profile folder), again after closing all OO processes.

[Villeroy]

I create a new document with Sheet1, Sheet2 and Sheet3 and put into Sheet2.B2 the following formula:
=Sheet1.A1 which refers to the value in the previous sheet, previous row, previous column.
When I copy or move the sheet to some other document behind the first sheet, the formula still refers to the value in the previous sheet, previous row, previous column.
When I copy or move the sheet to some other document to the first position, the result is #REF! and the formula is =#REF!.A1 which indicates that the sheet can not be resolved since there is no sheet previous to the first sheet.
##########################################################################################
I create a new document with Sheet1, Sheet2 and Sheet3 and put into Sheet2.B2 the following formula:
=$Sheet3.$A$1 which refers to the value in sheet #3, first row, first column.
When I copy or move the sheet to some other document with at least 2 existing sheets, the formula still refers to the value in sheet #3, first row, first column.
When I copy or move the sheet to some other document with only one sheet, the result is #REF! and the formula is =$#REF!.$A$1 which indicates that the sheet can not be resolved since there there are only 2 sheets in the document and the formula can no longer refer to sheet #3.

[kingfisher]

My mistake. I understood the issue related to copying a sheet within a document.

If you want to copy to another document, you will have to copy the sheets containing data before copying sheets containing formulas. One method to copy sheets to another file is the menu selection Insert > Sheet from file.

Or, more simply, the menu selection > File > Save as, then deleting any unwanted sheets.

[Villeroy]

Copying sheets within the same document or between different documents does not makes a big difference. Relative and absolute references fail when the relative or absolute sheet position is not accessible. Absolute ref to sheet #3 requires 3 sheets at least, relative reference to the next sheet requires that there is at least one more sheet behind the current one.
Similar error when you copy a relative reference to row #1 upwards or when you copy a relative reference to the first column to the left. The copied reference in the formula bar looks like #REF!1 when the column is not accessible and A#REF! when the row is not accessible.

[kingfisher]

Yes, understood. After the original post I copied a sheet which contained formulas to a new sheet in the same document. All formulas copied correctly including these by way of example:
=COUNTA($test.$A$1:$A$551)
=A25+IF(ISNUMBER(A14);T(STYLE("_bkYellow"));0)

The first would not copy correctly to a document which contains no sheet named 'test'. The second would not work correctly in a new document in which the style "_bkYellow" does not exist. Hence, my correction to my initial post when I realised that the OP may be referring to copying to another document.

We need examples of the formulas which have changed.

[Villeroy]

It copies correctly to a document which contains no sheet named "test" if there is some sheet at the same position as the sheet formerly known as "test".
When I copy a reference to $Sheet3.$A$1 into a document that started with German sheet names the same reference in the sheet copy refers to $Tabelle3.$A$1

And by the way: The original poster does not give us any hint what he does nor what the problem is.

[evilpilch]

Villeroy, why do you say: 'The original poster does not give us any hint what he does nor what the problem is.' When I stated quite clearly:

I am attempting to duplicate an entire sheet ... I thought the best way to do this would be to use the move/copy function and copy the sheet to a new location, however when I try this, the formulae all get screwed up and seem to refer to a cell 1 row down or 1 row across from the one I want (and the one that's in the sheet I copied from).

Now I'd have thought that the copy function should create a precise copy. But it seems not to. No matter if I use the copy sheet function, the copy / paste function or the copy / paste special function I get the same results - all my formulae are screwed up and the copied sheet refers to cells bumped down by one. Meaning I have to go through and copy & paste the correct formula CELL BY CELL! Which is laborious to say the least.

i.e. Just to break it down for you Villeroy, in case you, missed it:

What I'm trying to do is: COPY A SHEET (MAKE AN EXACT DUPLICATE)
How I'm trying to do it is: USING THE MOVE/COPY SHEET FUNCTION, USE THE COPY / PASTE and COPY / PASTE SPECIAL FUNCTIONS.
The net result of which is: THE FORMULA'S DON'T COPY ACROSS PROPERLY

Just to expand on this last point. If my original sheet (the one I'm trying to copy) has the formula: =B42+C42 in cell H56 ...when I copy the sheet (in any way, paste, paste special, move/copy sheet) the results in the new sheet are something like: C42 + D42 ...i.e. the values have all been shunted across one. Is this clear now?

Anyway, I'll try renaming the settings folder. I like the sound of that. I'm pretty certain its just some weird corrupted config file somewhere. Its that kind of weird problem.

Thanks for everyone's help though! I shall report back!

[Villeroy]

I only wrote how I tried to reproduce your problem but I can't. Somebody may be able to reproduce it when you attach a document where the problem occurs.

I thought the best way to do this would be to use the move/copy function and copy the sheet to a new location, however when I try this, the formulae all get screwed up and seem to refer to a cell 1 row down or 1 row across from the one I want (and the one that's in the sheet I copied from).

You did not tell if the new location is in the same document, in another document or in a new one. "Seem to refer" sounds strange, particularly when I fail to reproduce the problem. I forgot to mention that I tried with native Calc documents (.ods).

[kingfisher]

evilpilch, give us some details of the file such as number and position of sheets, where you want to place the copy sheet and a few examples of formulas which change and how they change. When you copy a cell to another cell, relative cell references change. When you copy a sheet to another sheet, relative sheet references change.

[evilpilch]

Okay, thanks Kingfisher...
The spreadsheet I'm working on has 27 sheets. Well 26, but I'm trying to duplicate one to make a few subtle changes. (95% of the sheet will remain the same, I just want the odd cell to point to a different reference on a different sheet).
I'm copying the final sheet, and trying to place the duplicate after it. I.e. making that the final sheet.
An example of the formulae going awry is: on the sheet which is okay (that I built from scratch and am attempting to copy is the 'Balance (Reasonable)' sheet). Cell B6 reads: =Costs.D4
But when I copy this sheet, the 'Balance (Reasonable)_2' sheet reads in cell B6: ='Balance (Reasonable)'.D4
In this case it has taken the name of the copied sheet, rather than copying exactly the formula (as I require).

Maybe I'm just being stupid, but how do I make an EXACT duplicate of a sheet. So all the formulae are exactly the same? I really need to sort this out fast as its taken me ages to manually build sheets which are 95% the same as previous sheets I have done, with but a few subtle changes.

Many thanks!

Also, Kingfisher, you advised me earlier to rename my settings folder and restart OO so that it rebuilds a new settings folder (which would presumably iron out any corruption in my current settings folder). Can you point me to the correct path to find my settings folder please. I can't seem to find it. Tho I am on very little sleep so probably just being stupid.

Thanks again!

[karolus]

Hi
I think there are many hints to absolute and relative Formulareferenzes in this Thread - don't you read it?

Put in a $sign in Front of the sheetname in the Formula:
=$Costs.D4

Karo

[MrProgrammer]

Hi, and welcome to the forum.

An example of the formulae going awry is: on the sheet which is okay (that I built from scratch and am attempting to copy is the 'Balance (Reasonable)' sheet). Cell B6 reads: =Costs.D4. But when I copy this sheet, the 'Balance (Reasonable)_2' sheet reads in cell B6: ='Balance (Reasonable)'.D4

I'm going to have to guess at this since the spreadsheet with the problem hasn't been attached to this topic, but I think the sheet order is Costs, 'Balance (Reasonable)', 'Balance (Reasonable)_2'. You've put =Costs.D4 in 'Balance (Reasonable)'.B6. If you read the tutorials on absolute and relative references, you'll learn that the formula in that cell really means "the cell two up and two to the right in the previous sheet". So when you copy that formula (via copy sheet) to sheet 'Balance (Reasonable)_2'.B6 "the cell two up and two to the right in the previous sheet" is ='Balance (Reasonable)'.D4, which is exactly what you see. If you want to make an absolute reference to sheet Costs, that would be =$Costs.D4.

Either change the sheet references to Costs on 'Balance (Reasonable)' to absolute sheet references before making the copy (hint: use Edit > Find & Replace) or change formulas with 'Balance (Reasonable)' to 'Balance (Reasonable)_2' or '$Balance (Reasonable)_2' after making the copy.

In this case it has taken the name of the copied sheet, rather than copying exactly the formula (as I require).

The copied sheet name happens to be the name of the sheet you copied in this case, but it's really formed as described above. Calc did copy the formula exactly, but the formula doesn't mean what you think it means.

when I try this, the formulae all get screwed up and seem to refer to a cell 1 row down or 1 row across from the one I want

Perhaps you should attach a document demonstrating that problem. It doesn't have to be the full 27 page spreadsheet, just a small sample that shows this "1 row down or 1 row across" difficulty that you're having.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[evilpilch]

Thanks for your help! I'll try the absolute reference $... sounds like it might just be the solution I was looking for. I'll let you know, and if need be, attach a sample of what I'm talking about. Thx again!

[evilpilch]

Can anyone tell me, is there a quick way to switch all the formulas in a sheet to refer to '$' absolute values? Or do I have to go through every cell and add in a $ before each sheet name?

[Villeroy]

Edit>Find&Replace?

[evilpilch]

Ah! It's amazing what you can find in the Help section. I discovered that Shift-F4 transforms formulae from relative to absolute and vice versa. So all good there! My sheet is now copying correctly with Absolute values. My thanks to everyone who was of assistance! Much appreciated!