Copy sheets

[col]

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.

MY PROBLEM

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.

[col]

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.

[burbuja]

this is useless. Try to describe it shorter and better

[bencomp]

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

[col]

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.

[mariosv]

=‘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.