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

-------------------

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48303