dynamic link

Hi Chris,

Let me know if you don't get a built-in Excel solution to this, because it would be very easy to design a very general-purpose custom macro to solve it. Of course, if the workbook the data are in is not open, the macro will have to open it, then pass the range object back to Excel--Excel cannot access data from a workbook (except for file properties) without opening it.

Damon

Hi Damon,

Thanks for your comment. Since my spreadsheet should contain about 10 of these dynamic links it would be nice if all the source documents don't have to be open(ed).
You don't have to open documents when you have a 'fixed' link!

Chris

Chris
One way to do this without openning the workbook
is via VBA.
Assuming
1) The name of the file is in A1
2) References are in A2:A11 (10 formulas)
3) Formula is in format =SUM('C:\ExcelFiles\Useful\[test.xls]Sheet1'!H6:H10)

Then in the worksheets events code module
Accessed via
1) Right clicking sheet tab
2) select view code
Paste this code in;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim D As String
Dim Fnm As String
Dim x As Integer

'Change this to your Directory address
D = "C:\ExcelFiles\Useful\"
Fnm = [A1].Text

If Target.Address = "$A$1" Then
If Dir(D & Fnm) = "" Then MsgBox "Not a valid File name!": GoTo Ex
Application.EnableEvents = False
For x = 1 To 10
Cells(x + 1, 1).Formula = "=SUM('" & D & "[" & Fnm & "]Sheet1'!H6:H10)" '"='" & D & "[" & Fnm & "]Sheet2'!A1"
Next
End If
Ex:
Application.EnableEvents = True
End Sub

What this routine does is to change your formulas
to the updated file name in A1 when ever you change A1.
If your situation is diff from your example then
post.........

Ivan

: Hi Chris,