Excel Spreadsheet sheet formulas

Question

I would like to be able to have a cell in a 2nd tab equal what was in a previous tab, then create a new tab, copy and paste and have the spreadsheet formula update to the previous tab instead of referencing the first tab.

Ie the formula for previous work in the 2 tab equals the value in the 1 tab completed to tab.

When I copy and paste into a new tab (3) to make the next progress sheet the sheet formula reference is still for the 1 tab instead of updating to the 2 tab. Is there a way around this?

score 3 · Answer 1 · answered May 28 '15 at 03:23

Excel can only reference sheets absolutely. Unlike cell references, which can be relative and change when copied, sheet references are always absolute.

Relative sheet references have been requested as a new Excel feature several times in the past, but so far not been implemented.

score 1 · Answer 2 · edited Mar 20 '17 at 10:17

Relative sheet referencing is fairly easy if you’re willing to use a little VBA. Create the following VBA function:

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

See How do I add VBA in MS Office? for general information on using VBA.

Then, if you want a cell in Sheet2 to access Sheet1!Q42, but then, when you copy from Sheet2 to Sheet3, you want the corresponding cell to access Sheet2!Q42, 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 Sheet(n), it will automatically refer to Sheet(n-1).

Excel Spreadsheet sheet formulas

2 Answers2