How to reference same cell in previous sheet in Excel?

Question

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 Sheet4), I want the same cell in 4 to reference the cell in 3.

Ordinarily, I'd have the cell in Sheet3 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.

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.

Answer 1

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

Answer 2

I didn't go through the entire J-walk document you posted but this is a pretty easy way to hard-code it. It uses the sheet's index to reference cells from other sheets instead of the sheet's name, so it doesn't matter if a sheet is renamed or if you don't know what the next sheet's name will be.

Function prevSheet()
  Dim i As Integer
  Dim j As Integer

  i = ActiveSheet.Index             ' Obtain current sheet index
  j = i - 1                         ' Determine the previous sheet's index
  oldValue = Sheets(j).Range("A1")  ' Obtain value from prev sheet's A1 cell 
  
  newValue =  oldValue + 1          ' Random operation to alter prev sheet's value
  Range("A1") = newValue            ' Display the new value on current sheet's A1 cell

End Function

Answer 3

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

Answer 4

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

Answer 5

You can do this with a defined name and formula. Although you're not adding any VBA code it will require saving the file with a macro compatible file type such as .xlsm.

Create a defined name. Name it wsNamesArray and in the "Refers To" box enter the following:

First, Create Two Defined Names

(This answer shows where to go in Excel to access defined names/named ranges.)

1st Name: wsNamesArray

=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) & T(NOW())

2nd Name: wsName

=MID(CELL("filename", INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,255) & T(NOW())

(Part of the formula, &T(NOW() doesn't change the result, but it makes the formula "volatile" which causes Excel to recalculate it after any change. This makes the formula update immediately after a sheet is renamed.)

Formulas

Now that you have created the defined names, you can use the following formulas in any cell:

Previous Sheet

=IF(  MATCH(wsName,wsNamesArray,0)-1 =0, ERROR.TYPE(7), INDEX(wsNamesArray,MATCH(wsName,wsNamesArray,0)-1))

Next Sheet

=IF(  MATCH(wsName,wsNamesArray,0)=wsCount, ERROR.TYPE(7), INDEX(wsNamesArray,MATCH(wsName,wsNamesArray,0)+1))

First Sheet

=INDEX(wsNamesArray,1) & T(NOW())

Last Sheet

=INDEX(wsNamesArray,COUNTA(wsNamesArray)) & T(NOW())

This Sheet

=wsName

2nd Sheet (note the 2 in the formula)

=INDEX(wsNamesArray,2) & T(NOW())

3rd Sheet (note the 3 in the formula)

=INDEX(wsNamesArray,3) & T(NOW())

Answer 6

=EOMONTH([worksheet reference],SHEET()-1)

First sheet in one workbook is named "Jan" with the date I reference in cell J4, so all J4 cells in subsequent worksheets use

=EOMONTH(Jan!J4,SHEET()-1)

First sheet in another workbook starts with "Apr" so each subsequent J4 cell has

=EOMONTH(Apr!J4,SHEET()-1)