Help with annual planning reports!

[MINI Cooper]

What would be the best way to update a large Excel file for next year
planning package? We do a 3-year plan which uses the following data:
Prior year actual 2002
Current year actual 2003
Plan year 1 2004
Plan year 2 2005
Plan year 3 2006
So for next year's plan each year will increase by 1. There are man
reports in the file which use the individual years data. Is there a
easier way to update this each year? Currently I am copying the prio
year's data and then pasting as values in a section below the curren
data. Formulas in the reports that use prior year data reference thi
area. The column headings are then increased by 1 in the top section
i.e. 2002 becomes 2003. The new year's planning data is then keyed int
the top section. I inherited this spreadsheet and was wonder if ther
was a way to change the formulas in the reports to look for year 1
year 2, etc. That way each year I could just add a column to the dat
area for the next year out.
Thank

 

[Andre Croteau]

Hi,

I have always fount it very useful to have a MASTER reference date in ONE
CELL ONLY, and have all the other DATE cells refer to it. That way, you
don't have to worry if the other references have been modified

Say on Sheet1!A1, I put the year 2004

In the Prior year Actual sheet, all date cells could be something similar
to: ="prior Year Actual "&Sheet1!A1-2
If date values are used, you can say =date(Sheet1!A1-2,1,1) for Jan 2002

For the other sheets, you do the same: Plan year 3 would be ="Plan
Year "&Sheet1!A1+2

So by the time you do the exercise next year, all you have to do is modify
the MASTER cell Sheet1!A1 to 2005

HTH

André