You could use this User Defined Function.

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function


Gord Dibben MS Excel MVP

On Sat, 3 Jun 2006 09:07:55 -0500, mattylance
wrote:


I do production planning and I want a tab to represent each week. Wk1
closing inventory feeds to wk 2 opening inventory and so on.

It is easy to set this up manually, but I would like to be able to have
a template tab with the formula always linking to the previous tab.
Therefore, when I want to setup wk 3, I want to be able to copy and
paste the default tab, and have it automatically link to the previous
tab.

This would save me a whole lot of time each week. Anybody have
suggestions?

Gord Dibben MS Excel MVP