Create a user-defined function SheetSum that loops through the sheets
and sums the relavant ranges. here's the code :

Function SheetSum(ByVal InData As Range) As Variant
Dim wsCurrent As Worksheet
Dim CurSum As Double

CurSum = 0
For Each wsCurrent In Worksheets
CurSum = CurSum + wsCurrent.Range(InData.Address).Value
Next wsCurrent
SheetSum = CurSum
End Function

to call this function to add , for example, the contents of range A2 in
all sheets, enter this formula = SheetSum(A2).

HTH

Fadi
www.chalouhis.com/XLBLOG