Ask Your Question

Copy sheets [closed]

asked 2012-10-09 19:21:45 +0200

anonymous user


updated 2013-01-31 19:18:04 +0200

qubit gravatar image

I have my accounts on libreoffice calc. Each year I start a new calc spreadsheet and each spreadsheet has 52 sheets (1 year).

Each week I simply add a new sheet and name it week number * (1 to 52). I like a running record of past years takings so I have a cell that shows this. This cell adds the same cell from the previous sheet to the same cell in last years calc sheet. So week 3 will add week 2 2012 (a cell having 2011’s running total) to a cell in week 3 2011.

This is the formula in the cell-

=‘2 13 SEPT 12’.B88+‘file:///C:/Users/books/BOOKS YEAR 7 09 10.ods’#$‘3 20 SEPT 09’.B62

Up until now I’ve used Lotus 1-2-3 and it worked well but could not be upgraded to my new laptop.


On the new sheet, when I copy to make week 4 27 SEPT 12, whilst the first part updates to be 3 20 SEPT 12 the second part stays at week 3 but I need it to also move on one week.

Hope you understand all of this.

I recall from my distant past that $ or # locked or unlocked things but forget what does what. Trying to explain this better. It is, I’m sure, to do with absolute and relative referencing.

So in cell B2 of sheet 2 I want to add the number in B2 of sheet 1 plus the number in cell B2 of sheet 2 of another spreadsheet. Then when I copy sheet 2 to produce sheet 3 I need to automatically reference cell B2 sheet 2 plus I need the second half of the sum to move up a sheet to B2 sheet 3 of the other spreadsheet. Tried ‘shift+F4’ but no difference. Tried just removing the $ but nope.

Thanks for your time at least trying to understand this.

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 2015-10-21 02:24:24.203862


Mariosv. Should work. From my days of Lotus 123 programming I thought it was correct. I've tried all combinations of removing $, # or ' but not doing as it should.

col gravatar imagecol ( 2012-10-11 20:18:01 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2012-10-10 22:32:37 +0200

m.a.riosv gravatar image

=‘2 13 SEPT 12’.B88+‘file:///C:/Users/books/BOOKS YEAR 7 09 10.ods’#$‘3 20 SEPT 09’.B62

The $ before ‘3 20 SEPT 09’.B62, make the reference to the sheet absolute not relative, then when you copy do not change.

This must work as in 123, no special formulas.

edit flag offensive delete link more

answered 2012-10-09 21:05:05 +0200

burbuja gravatar image

this is useless. Try to describe it shorter and better

edit flag offensive delete link more


This doesn't answer the question. Try to use comments to stimulate the original poster to make the question clearer. Thanks :)

bencomp gravatar imagebencomp ( 2014-06-29 21:54:17 +0200 )edit

answered 2012-10-10 10:40:09 +0200

col gravatar image

When copying a sheet I want a cell to equal the same cell on the previous sheet plus the cell on another worksheet. When copying again I want all cells to move on one sheet but the cell stays on the first sheet of the external worksheet.

edit flag offensive delete link more

Question Tools


Asked: 2012-10-09 19:21:45 +0200

Seen: 1,796 times

Last updated: Jan 31 '13