Excel: portable formula to reference data on previous (relative) worksheet

Question

I need to create a fairly simple Excel spreadsheet (workbook). I need 12 sheets (tabs), one for each month.

Each sheet references cells in the previous sheet. For example, the "May" sheet is drawing data from the "April" sheet, the "June" sheet will draw data from the "May" sheet, etc.

Let's say I create the first and the second sheets, "April" and "May". If I copy "May" and I rename it "June", all the formulas will still refer to "April" as in the "May" sheet. I would like that they refer automatically to "May" instead.

How is it possible to do this?

Answer 1

Create the following macro:

Function SHEET_OFFSET(Offset, Ref)
'   Returns cell contents at Ref, in sheet Offset
    Application.Volatile
    With Application.Caller.Parent
        SHEET_OFFSET = .Parent.Sheets(.Index + Offset) _
         .Range(Ref.Address).Value
    End With
End Function

Then, if you want the May sheet to access the April!Q42 cell, use the formula

SHEET_OFFSET(-1, Q42)

The first argument is the sheet number, relative to the current one, and the second argument is the cell reference.  When you copy this formula to the June sheet, it will automatically reference the May sheet.

Source: I found this at The Spreadsheet Page.