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

"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
>
>

[bg]

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


"derrida derider" <[email protected]> wrote in
message news:[email protected]...
> 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
>>
>>