How can I sum a cell in multiple worksheets as new ones are created.

[lawhesl]

I use a worksheet template to track stock trades and create a ne
worksheet for each trade. I would like to sum the same cell on eac
worksheet. How do I automatically add a new worksheets cell to the su
of the existing worksheets??

Thank

 

[duane]

if you insert the new worksheet in between the "range" of summe
worksheets it should be picked up in the su

 

[Gord Dibben]

Copy/paste this UDF to your workbook.

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

In a cell on your new worksheet, say A1, enter =PrevSheet(B2)

A1 on each sheet will refer to B2 on the previous sheet.

You could open a new workbook and delete all but one sheet.

Enter the formula in A1.

Save this book as Save As type Template(*.xlt)

Name it SHEET(Excel will add the .xlt extension.

Store SHEET.XLT in your XLSTART folder.

It will become the default new sheet when you Insert>Worksheet.

Gord Dibben Excel MVP