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

Right click on any worksheet tab and click on "VIEW CODE"

Place the following code into MODULE 1. (if it's not there just click INSERT and MODULE)

Code

Function oldsheet(rng As Variant)
    Prevsn = "Sheet" & Val(Mid(ActiveSheet.CodeName, 6, (Len(ActiveSheet.CodeName) - 5))) - 1
    With Sheets(Prevsn)
        oldsheet = .Range(rng)
    End With
End Function

And the new sheet the following formula

=oldsheet("J30")

Posted as is from link just in case it gets deleted.

Ref pulled from http://www.mrexcel.com/forum/excel-questions/399317-formula-referencing-previous-sheet.html

How to reference same cell in previous sheet in Excel?

Related

Recent Posts