Excel allows you to refer to cells on other worksheets, but these links are not relative; there is no way to refer to the next or previous sheet without hard coding that sheet name in the formula. And if the user changes the order of the sheets or inserts a sheet, the formula will no longer refer to the next or previous sheet.

This page describes some VBA procedure that you can use to refer to the first, next, previous, or last worksheet in a workbook.   These functions use the Application.Caller property, so they will not work unless they are called directly from worksheet cells.   

These functions don't use ActiveSheet or ActiveWorkbook.  Instead, they go through the Parent objects of the Application.Caller properties.  Therefore, they will work regardless of what the active workbook or worksheet happens to be, and regardless of whether the formulas themselves resided in the same workbook as the cells which call them.  

Returning The Number Of Worksheets In A Workbook 

The following function will return the number of worksheets in the workbook which is calling the function.  

Function SheetsCount() As Integer
    Application.Volatile True
    SheetsCount = Application.Caller.Parent.Parent.Worksheets.Count
End Function

Returning The Index Of The Worksheet

The following function will return the index (position) number of the worksheet which is calling the function.

Function SheetPosition() As Integer
    Application.Volatile True
    SheetPosition = Application.Caller.Parent.Index
End Function

Returning The Name Of The Current Worksheet

The following function will return the name of the worksheet which is calling the function. 

Function ThisSheetName() As String
    Application.Volatile True
    ThisSheetName = Application.Caller.Parent.Name
End Function

Returning The Name Of The First Worksheet In The Workbook 

The following function will return the name of the first worksheet in the workbook which is calling the function.

Function FirstSheetName() As String
    Application.Volatile True
    With Application.Caller.Parent.Parent.Worksheets
        FirstSheetName = .Item(1).Name
    End With
End Function

You can then use this name in an INDIRECT formula.  For example to return the value of cell A1 from the first worksheet, use 

=INDIRECT(FirstSheetName()&"!A1")

Returning The Name Of The Last Worksheet In The Workbook 

The following function will return the name of the last worksheet in the workbook which is calling the function.

Function LastSheetName() As String
    Application.Volatile True
    With Application.Caller.Parent.Parent.Worksheets
        LastSheetName = .Item(.Count).Name
    End With
End Function

You can then use this name in an INDIRECT formula.  For example to return the value of cell A1 from the last  worksheet, use 

=INDIRECT(LastSheetName()&"!A1")

Returning The Name Of The Previous Worksheet

The following function will return the name of the previous worksheet. If the function is called from a worksheet cell, the sheet name is enclosed in single quotes (apostrophes). If the function is not called from a cell, the name is not enclosed in quotes.

Function PrevSheetName(Optional ByVal WS As Worksheet = Nothing) As String
    Application.Volatile True
    Dim S As String
    Dim Q As String
    If IsObject(Application.Caller) = True Then
        Set WS = Application.Caller.Worksheet
        If WS.Index = 1 Then
            With Application.Caller.Worksheet.Parent.Worksheets
                Set WS = .Item(.Count)
            End With
        Else
           Set WS = WS.Previous
        End If
        If InStr(1, WS.Name, " ", vbBinaryCompare) > 0 Then
            Q = "'"
        Else
            Q = vbNullString
        End If
    Else
        If WS Is Nothing Then
            Set WS = ActiveSheet
        End If
        If WS.Index = 1 Then
            With WS.Parent.Worksheets
                Set WS = .Item(.Count)
            End With
        Else
            Set WS = WS.Previous
        End If
        Q = vbNullString
    End If
    PrevSheetName = Q & WS.Name & Q
End Function

If this function is called from the first worksheet in a workbook, the name of the last worksheet will be returned.  In other words, it will "loop" back around to the last worksheet, rather than returning an error.   You can then use this name in an INDIRECT formula.  For example to return the value of cell A1 from the previous  worksheet, use 

This function will return the name of a sheet based on its position. If the function is called from a worksheet cell, and there is a space in the sheet name, the sheet name is enclosed in single quotes (apostrophes). If it is called by another VBA procedure, the sheet name is not enclosed in single quotes. If you are calling this from another VBA procedure, you may specify a workbook reference in which the sheet is located.

Function SheetNameOfIndex(Ndx As Integer, Optional WB As Workbook = Nothing) As String
    Dim Q As String
    Dim S As String
    Application.Volatile True
    If IsObject(Application.Caller) = True Then
        S = Application.Caller.Parent.Parent.Worksheets(Ndx).Name
        If InStr(1, S, " ", vbBinaryCompare) > 0 Then
            Q = "'"
        Else
            Q = vbNullString
        End If
    Else
        S = IIf(WB Is Nothing, ActiveWorkbook, WB).Worksheets(Ndx).Name
        Q = vbNullString
    End If
    SheetNameOfIndex = Q & S & Q
End Function