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!
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments