ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

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 worksheet

It'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 Function
Examples:
=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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2024    Ole P. Erlandsen   All rights reserved
E-mail Contact Address