Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
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'.