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é
"MINI Cooper >" <<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> What would be the best way to update a large Excel file for next years
> 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 many
> reports in the file which use the individual years data. Is there an
> easier way to update this each year? Currently I am copying the prior
> year's data and then pasting as values in a section below the current
> data. Formulas in the reports that use prior year data reference this
> 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 into
> the top section. I inherited this spreadsheet and was wonder if there
> 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 data
> area for the next year out.
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
Similar Threads
