relative sheet pt2

[snax500]

I originally wrott this message:

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

I got this answer:

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

The only problem is that I am referencing other workbooks. SHEETOFFSET
does not seem to work if I am referencing external cells.

Any other ideas?

Thanks again.

 

[Leith Ross]

Hello snax500,

Copy and Paste this macro into a VBA project module. The examples wil
show you how to use it on a worksheet and the resulting formula. Yo
will have to change the names of the 2 workbooks to match your own t
make this work. The cells will change when you Formula Fill a range.

USING THE MACRO
Sheet1 is active sheet
Enter macro into Cell A2 "=AddCells(A1)"
Formula for Cell A2 "=[Book2.xls]Sheet1!A1+[Book1.xls]Sheet1!A1"

Sheet2 is active sheet
Enter macro into Cell A2 "=AddCells(A1)"
Formula for Cell A2 "=[Book2.xls]Sheet2!A1+[Book1.xls]Sheet2!A1"


Code
-------------------
Public Function AddCells(Cell As Excel.Range)

Dim cAddress As String
Dim cFormula As String
Dim shtName As String
Dim Wkb1 As String
Dim Wkb2 As String

'Change these Workbook names to match what you will be using
Wkb1 = "Book1.xls"
Wkb2 = "Book2.xls"

shtName = ActiveSheet.Name
cAddress = Cell.Address(False, False)
cFormula = "=[" & Wkb2 & "]" & shtName & "!" & cAddress _
& "+[" & Wkb1 & "]" & shtName & "!" & cAddress

Test = cFormula

End Function

 

[Leith Ross]

Hello snax500,

Copy and paste this macro into a VBA project module. This functions
just like a normal Excel Worksheet Function. Change the Workbook Names
in the Macro to the ones you are using (I assumed they would stay the
same).

USING THE MACRO:
Cell A2 Formula "=AddCells(A1)"
Cell A2 Result = "[Book2.xls]Sheet1!A1 + [Book1.xls]Sheet1!A1"

Code:
--------------------
Public Function AddCells(Cell As Excel.Range)

Application.Volatile

Dim cAddress As String
Dim cResult As String
Dim shtName As String
Dim Wkb1 As String
Dim Wkb2 As String

'Change the Workbook Names to match your own
Wkb1 = "Test Book1.xls"
Wkb2 = "Test Book2.xls"

shtName = ActiveSheet.Name
cAddress = Cell.Address(False, False)
cResult = Workbooks(Wkb2).Worksheets(shtName).Range(cAddress).Value _
+ Workbooks(Wkb1).Worksheets(shtName).Range(cAddress).Value

AddCells = cResult

End Function