I'm doing a monthly accounts workbook – each month on a separate spreadsheet – and on my final spreadsheet in that workbook I want a table that shows month by month numbers, altogether for easy comparison.

I've set one up for last year's accounts, but now I'm doing this years. When I copy the final totals spreadsheet from last year to this year's workbook, it refs back to last year's .xls.

1. How can I stop it from reffing back to last year?
2. When I create one cell that has the correct formula, eg. =AprilD64, and then click the bottom right handle and drag across or down, it changes the number, ie. =AprilD65, AprilD66. I want it to fix the number, and change the month ie. =MayD64, JuneD64 etc.

Please help. Thanks.

i am guessing that if you look at the formula it contains a reference to last years workbook, if you look at the formula is there any mention of .xls in there?

when you say aprilD64 do you mean =APRIL!D64? in which case change it to =APRIL!D$64

the dollar will lock the cell reference

When you copy the final totals spreadsheet from last year's workbook to this year's the formulae will include a reference to last year's workbook. Something like:

='[lastyear.xls]April'!D64

After you've copied the sheet you can do a Find and replace to remove the reference to last year's work book, i.e.

Find: [lastyear.xls]
Replace: {leave blank}
Look in: Formulae

Before running the replace make sure the worksheet names in both workbooks are identical otherwise you'll get a dialogue box for each cell where it can't find the source cell. Best to click the 'Replace' button first a couple of times to check it's working properly before hitting 'Replace All'.