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

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

Thanks


--
lawhesl
------------------------------------------------------------------------
lawhesl's Profile: http://www.excelforum.com/member.php...o&userid=16798
View this thread: http://www.excelforum.com/showthread...hreadid=319978

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=319978

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 InsertWorksheet.

Gord Dibben Excel MVP

On Tue, 23 Nov 2004 21:43:50 -0600, lawhesl
wrote:


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

Thanks