relative sheet macro

[snax500]

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

 

[derrida derider]

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

 

[bg]

It doesn't appear to work when you are referencing different workbooks.

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

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