|
||||
These pages are no longer updated and are only available for archive purposes.Click here to visit the pages with updated information. Add values from the previous or next worksheetIt's possible to add values from a range in another worksheet like this: =SUM(Sheet1!A1:A100) This formula could then be copied to the other sheets in the workbook, but you would have to edit all the sheet names manually. With the user defined functions below you can enter the function once (e.g. if you group the worksheets first) and skip that manual editing of the worksheet name in the formula: Function SumPreviousSheet(InputRange As Range) As Double ' adds the values in InputRange in the previous worksheet ' returns 0 if the function is used in the first worksheet Application.Volatile ' omit if not necessary SumPreviousSheet = 0 On Error Resume Next SumPreviousSheet = _ Application.WorksheetFunction.Sum(InputRange.Parent.Previous.Range(InputRange.Address)) On Error Goto 0 End Function Function SumNextSheet(InputRange As Range) As Double ' adds the values in InputRange in the next worksheet ' returns 0 if the function is used in the last worksheet Application.Volatile ' omit if not necessary SumNextSheet = 0 On Error Resume Next SumNextSheet = Application.WorksheetFunction.Sum(InputRange.Parent.Next.Range(InputRange.Address)) On Error Goto 0 End Function You can also create a function that lets you add in another worksheet like this: Function SumOffsetSheet(InputRange As Range, Optional SheetOffset As Integer = 0) Application.Volatile SumOffsetSheet = 0 On Error Resume Next SumOffsetSheet = _ Application.WorksheetFunction.Sum(Worksheets(InputRange.Worksheet.Index + _ SheetOffset).Range(InputRange.Address)) On Error Goto 0 End Function Or you can create a function that lets you add in another worksheet like this: Function SumIndexSheet(InputRange As Range, Optional SheetIndex As Integer = 0) Application.Volatile SumIndexSheet = 0 If SheetIndex = 0 Then SumIndexSheet = Application.WorksheetFunction.Sum(InputRange) Else On Error Resume Next SumIndexSheet = _ Application.WorksheetFunction.Sum(Worksheets(SheetIndex).Range(InputRange.Address)) On Error Goto 0 End If End FunctionExamples: =SumPreviousSheet(A1:A100) will add the values in the range A1:A100 in the previous worksheet. =SumNextSheet(A1:A100) will add the values in the range A1:A100 in the next worksheet. =SumOffsetSheet(A1:A100,1) will add the values in the range A1:A100 in the next worksheet. =SumOffsetSheet(A1:A100,-1) will add the values in the range A1:A100 in the previous worksheet. =SumIndexSheet(A1:A100,2) will add the values in the range A1:A100 in the second worksheet. =SumIndexSheet(A1:A100) will add the values in the range A1:A100 in the active worksheet.
Document last updated 1999-08-14 12:44:13 Printerfriendly version
|
||||
|