Formula ignoring sheet name but still referencing sucessive sheets in workbook

No, there is no native function to do that. But a custom function can be easily designed to do that. This is VBA, but used as cell function. You would have to add this function trough an ADDIN for you computer, or install it permanently in these workbooks, making them .xlsm files. These functions would go in a standard code module (Insert > Module):

Function NEXTSHEET(MyRef As Range) As String
Dim shIdx As Long
Application.Volatile

    shIdx = Application.Caller.Parent.Index
    If shIdx = Sheets.Count Then
        NEXTSHEET = "no further sheets"
    Else
        NEXTSHEET = Sheets(shIdx + 1).Range(MyRef.Address).Value
    End If
End Function

Function PREVSHEET(MyRef As Range) As String
Dim shIdx As Long
Application.Volatile

    shIdx = Application.Caller.Parent.Index
    If shIdx = 1 Then
        PREVSHEET = "No prior sheets"
    Else
        PREVSHEET = Sheets(shIdx - 1).Range(MyRef.Address).Value
    End If
    
End Function

These two functions would be used in a cell like:

=NEXTSHEET(B12)
=PREVSHEET(C15)

Just put the cell reference you want inside the function.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.