Forum Discussion
How can I make a copy of a spreadsheet in which the sheet reference is relative?
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.
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.
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
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.