Copy sheets

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

col
1 ● 1

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

qubit
5693 ● 3 ● 48 ● 41

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.