Ask Your Question
0

copy all the formulas from one sheet to another sheet in another ods [closed]

asked 2014-03-19 17:48:36 +0200

jleslie48 gravatar image

so I have a a spreadsheet that is the 1040 tax documents. it has some 30-40 sheets. it gets released every year.
it has references like "wages" "interest_income" etc.

from that starting point, I make up a new sheet that in "mystatetaxes" and calculate my state taxes. here is a snippet of the sheet:

12a $2
12b $2 calcs:
13
14 $150,068 126,824.00 23,244.00
15 $0 0
16 $0 0 126,824.00
17 $23,940 23940
18 $0 0

now this sheet has formulas in it. if I go to tools->options->calc and can turn on view formulas. here is that exact same snipped in formula view:

12a =+B6+B7+B8+B11
12b =+B9+B10 calcs:
13
14 =Wages =B15-$'W-2s'.G26 =$'W-2s'.G26
15 =Interest_Inc 0
16 =Dividend_Inc 0 =B15-F15
17 =Business_Profit =B18
18 =B63 0

the problem is if I cut and paste the snippet into a new ods file, on a new sheet this is what I get:

12a $2
12b $2 calcs:
13
14 #NAME? #NAME? 0.00
15 $0 0
16 $0 0 #NAME?
17 $0 0
18 $0 0

and the formula view:

12a =+B6+B7+B8+B11
12b =+B9+B10 calcs:
13
14 =Wages =B15-$Disclaimer.G26 =$Disclaimer.G26
15 =Interest_Inc 0
16 =Dividend_Inc 0 =B15-F15
17 =Business_Profit =B18
18 =B63 0

so I've got two questions,

1) why didnt the cell with "=Wages" work and give me a #NAME? error? if I go into that cell (F2) and copy and paste that line, into itself, It fixes itself and references the cell correctly: 12a $2 12b $2 13
14 $146,749 15 $0

2) what's with the disclaimer?? how can I get it tow just copy the cell exactly as if I typed it? If I go into the cell of the original sheet, edit the cell (F2) and select the whole TEXT cut it, and then go to the new sheet in the new ods document, go to the corresponding cell do a (F2) and PASTE it fixes the cell. I want this for the whole sheet.

any ideas ?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 08:09:59.696421

1 Answer

Sort by » oldest newest most voted
0

answered 2014-03-19 23:42:44 +0200

m.a.riosv gravatar image

To copy/move a sheet from between two files:
Select the sheet(s) to copy.
Menu/Edit/Sheet/Move-Copy (or right-click on the sheet-tab)
there you have the options to copy/move to a new file, or to other open file, or to a new position in the same file.

edit flag offensive delete link more

Comments

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 gravatar imageKing_ZZ ( 2015-09-04 20:34:35 +0200 )edit

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 gravatar imageKing_ZZ ( 2015-09-04 20:51:29 +0200 )edit

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

King_ZZ gravatar imageKing_ZZ ( 2015-09-04 20:57:12 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2014-03-19 17:48:36 +0200

Seen: 2,033 times

Last updated: Mar 19 '14