Use the SHEETOFFSET function that John Walkenbach created (I use it
regularly). Details are at http://www.j-walk.com/ss/excel/tips/tip63.htm

"snax500" wrote:

In Excel2000, I have a summary file that adds specific cells from two
other files (they are all the same amount of sheets). I want to be able
to create a formula ( +[Book2]Sheet1!A1+[Book1]Sheet1!A1) in Sheet1.
Once I have this formula, I want to copy it from sheet to sheet so that
on sheet2 it reads =+[Book2]Sheet2!A1+[Book1]Sheet2!A1. I want to a
macro to change the sheet # relative to the file.

Thanks