Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I know how to reference a cell in another sheet in Excel, but I'd like to know if there's some way to automate this process, such that if I duplicate a sheet, it will update its references to the previous sheet in sequence.

For example, let's say sheet n has a cell that references a cell in sheet n-1. If I have sheets 1, 2, and 3, I want the cell in 3 to reference a cell in 2. Then, if I duplicate 3 (making Sheet 4), I want the same cell in 4 to reference the cell in 3.

Ordinarily, I'd have the cell in sheet 3 read, 'Sheet2'!A1. Then, if I duplicate Sheet3 to make Sheet4, I'd have to go into that same cell on Sheet4 and change it from 'Sheet2'!A1 to 'Sheet3'!A1. I'd rather have this change done automatically when I duplicate the sheet.

Sorry for the long-winded explanation, but is there a way to automate this process rather than having to do it by hand? I'm currently using Excel 2013 preview but I also have Excel 2012.

Thank you!

share|improve this question
    
Nevermind, found my solution right here: j-walk.com/ss/excel/tips/tip63.htm . Turns out macros are extremely powerful! :) – Argus9 Dec 26 '12 at 2:53
1  
Perhaps you can answer your own question, so that the rest of the world can benifit as well!! – Jacob Jan Tuinstra Dec 26 '12 at 5:27
    
I did, check my comment above. – Argus9 Dec 26 '12 at 19:06
1  
You've answered it as a comment. If you answer it as an answer, people tend to notice it better and can benefit from it. – Jacob Jan Tuinstra Dec 26 '12 at 19:14
    
Ah, I gotcha. Thanks for the tip! – Argus9 Dec 26 '12 at 19:48
up vote 1 down vote accepted

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
share|improve this answer

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

share|improve this answer

Use in cell like =Prev(J30)

Function Prev(ByRef r As Range) As Variant
Application.Volatile
Prev = r.Parent.Previous.Range(r.Address).Value
End Function
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .