How to reference same cell in previous sheet in excel?

I found my solution right here: j-walk.com/ss/excel/tips/tip63.htm . Turns out macros are extremely powerful! :)

You can create a function called sheetoffset in VBA to do this

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