Generically reference the previous workssheet

[John]

I have a workbook w/ several sheets. A few cells in each sheet accumulative
sums of data from all the previous sheets. I've been doing this w/ a cell
formula like "=C14+SheetN!F14" where SheetN is the name of the previous
sheet. If I reorder the sheets or add a sheet in between others, I have to
resetup all affected cum cells since the name of the previous sheet changed.

Is there a way I can change this formula to something like
"=C14+(currentsheet-1)!F14" to generically reference the previous sheet

Thx for your help, John

 

[Gord Dibben]

John

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
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

=C14+PrevSheet(F14)


Gord Dibben MS Excel MVP