Copying formulas from document to document

[King_ZZ]

*Edit ***

Basically I was having problems copying formulas I had spent ages making, onto other sheets and documents and they ended up referencing all the wrong locations.

I have since solved this myself and then pasted the answer because don’t you just hate it when there is loads of questions out there, then it gets solved and the person doesn’t bother coming back to explain how it was solved…

Give something back to the community people its good enough to help you out.

Ask not what the forum can do for you, but what you can do for the forum.

skip to my answer now.

*Edit over ***

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 have switched to libreoffice as I think it has more functionality with using coded spreadsheets?

I am having a problem with libreoffice calc copying formulas from one sheet to another

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

*** deleted examples as not needed anymore ***

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 example $sheet1.A1 because I thought if I moved a sheet or added a sheet all my formulas will stay connected to each other and not change the sheets.

[AlexKemp]

RFO

[King_ZZ]

For anyone else this is how the locking $ works.



If you copy or move it will do the same so I will only mention one of them.

If you copy a cell to another sheet or document it will be the same except

in the same document it will reference the > sheet > cell(s)

and in the other document it will reference > File location > file > sheet > cell(s)



If I have this in part of my formula this is what will happen;

(lets use an example; I have the formula in cell B2)


A1 - If you move this to the same cell location (in this example B2) but on another sheet it will not change



$A1 - If you move this to another sheet in a different space the number will move up or down or be invalid as #REF! For example if I copied it to cell G5 it would be $A4 because the number has moved down from cell B2 and the letter is locked so moving left or right will not change the formula.



A$1 - Same as above if I moved this to cell G5 from B2 it would become F$1 as the letter has moved across 5 spaces. If I copied this to cell A2 it would show #REF! because it is referencing a cell before column A which does not exist.



$A$1 - Same as above if I copied this to cell G5 it would still be $A$1 this would not change wherever you copied it to and should never become a #REF! error.





If you lock a sheet for example; (all the letters and numbers work the same as mentioned previously)



$Sheet1.A1 - this will always reference that sheet if you past the formula in any other sheet or if you paste it in any other document it will reference the sheet inside the document it was referencing before it was copied



Sheet1.A1 - if you do not lock a sheet and copy it from sheet2 to sheet4 for example it will then show up as this Sheet3.A1 as it has used its relative position compared to the sheet it was originally on, it has counted two more sheets right as you have pasted in two sheet to the right.

The same is true if you paste this into another document on sheet4 (from sheet2 on previous document) it will reference two more sheets right of the document you are pasting into - so it will reference whatever sheet3 is called on that document




** The End **

[King_ZZ]

If you found this helpful please tick it as solved or the up arrow as I get points or something like that I have no idea what happens yet.