How can I make a copy of a spreadsheet in which the sheet reference is relative?

[Blorgo]

I want to create a series of copies of a spreadsheet.  Cell A1 on Sheet 2 will reference cell A1 on Sheet 1.  When I copy Sheet 2 to Sheet 3, cell A1 on Sheet 3 references cell A1 on Sheet 1 rather than Sheet 2.  Ideally, the sheet name would be a variable and every copy would increase the Sheet number by one.  Is there a simple solution - assuming I've made the problem clear?

[#2 · Accepted solution]

@Blorgo 

You could make use of the INDIRECT function to do this, and the ease with which it would work would depend on your naming convention for those various sheets. Be aware that although INDIRECT can be very helpful (I use it fairly extensively in one workbook), it's what's called a volatile function, meaning it can slow down a really large workbook.

[#3]

@mathetes 

Thanks, mathetes.  I was able to use the Indirect function and it simplified greatly the work of copying a series of spreadsheets so that each refers to the previous sheet.  I still had to manually enter the name of the previous sheet on each new sheet for the function to work, but that was the only real additional input required (besides changing the sheet name).  So, again, thanks!

Blorgo

[#4]

@Blorgo 

As I said in my first reply, "the ease with which it would work would depend on your naming convention for those various sheets." In the workbook where I make my most extensive use--to track investment activities--the individual sheets are named by the stock symbol (MSFT, APPL, etc) and I have a summary sheet where those symbols appear in a list, and that list is the reference used by the INDIRECT function that pulls data from each sheet on to a separate row of the summary sheet. So, yes, you do need to enter the names for INDIRECT, but once done for the first time a new sheet is created, it can serve for a long time.