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.