Linking a cell to the previous tab?

[mattylance]

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]

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
<[email protected]> 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

[mattylance]

I can't tell you how much time this is going to save me!

Thanks again for your help.