relative sheet updating help

[bryonwoods30]

Just wondering why excel doesn't do this like lotus, or if there is an option to make excel do it?
I have 3 tabs, year1, year2,year3. year3 being the current year. I have a ton of formulas in year3 that compare year3 to the previous year.
We have just gone into a new year, so I inserted a new tab year4 and copied the formulas from year3 tab to year4 hoping it would update the sheet so that it compared year4 to year3 the same way year3 was comparing to year2. Is there a way to make excel do this like lotus used to do automatically?
i have given you just a simple example of what i am looking to do. I actually have a few thousand formulas that look at multiple sheets for comparisons and when i add a new sheet I need them to update without having to risk errors from find and replace.

 

[Damon Ostrander]

Hi Bryon,

Here is a user-defined function (UDF) that can help you do this. I'm assuming that your worksheets are named, e.g., 2008, 2009, 2010, 2011, etc. This enables the code to calculate the previous year worksheet's name by subtracting 1 from the current year worksheet name.

First, here's the UDF:

Function PrevYR(R As Range) As Range
   'Converts a reference to a cell on a Year worksheet to
   'a reference to the same cell on the previous year's worksheet.
   'This assumes the worksheets are named, e.g., 2010, 2011, 2012, etc.
   Dim NewRref    As String
   Application.Volatile
   NewRref = R.Parent.Name - 1 & "!" & R.Address
   Set PrevYR = Range(NewRref)
End Function

You can use this on the current year worksheet to give the value of a cell reference on the previous year worksheet like this:

=PrevYr(A1)

so if you use this on the 2011 worksheet, it will yield the value of cell A1 on the 2010 worksheet.

You can also use this function in your formulas. For example.

=12*(B4-PrevYr(B4))

which subtracts the previous year value from the current year value in cell B4, and multiplies this difference by 12.

To install this UDF in your workbook, simply go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane.

Keep Excelling.

Damon