reference to previous worksheet

  • Thread starter Thread starter Daniel Lidström
  • Start date Start date
D

Daniel Lidström

Hi,

how do I reference a cell (say f39) in the previous worksheet? I want to
accumulate values from each worksheet to the next one. Something like
=$previous_sheet$f39+sum(f1:f38)
Thanks!
 
Assuming you don't want to specify the name explicitly, but use a formula to
derive it:
There is no specific support for this. If you sheets have some type of
sequence inherent in the name, you could parse out the sequence of the
current sheet using Cells("filename",A1) as a start, then subtract 1 from
that and build the previous sheet name.
 
Daniel

User Defined Function.......

'in sheet2, enter =Prev("C3")+C3
'which would add C3 from sheet1 to C3 of sheet2. If you
'filled this thru all 30 tabs, each would refer to the previous sheet.

Function Prev(rg As String)
Application.Volatile True
If Application.Caller.Parent.Index = 1 Then
Prev = 0
Else
Prev = Sheets(Application.Caller.Parent.Index - 1).Range(rg).Value
End If
End Function

In your specific case.......

=Prev("F9")+SUM(F1:F38)

Gord Dibben Excel MVP
 
Back
Top