MrExcel Publishing
Your One Stop for Excel Tips & Solutions

dynamic link


Posted by Chris on August 26, 2001 11:55 AM

I want to make a dynamic reference to another document depending on the (changing)content of a cell. Example: content A1= "ce". formula in cell A2 should now read =SUM('[ce.xls]sheet1'!D1:D10) If A1=df then a refernce to df.xls is required.
The 'INDIRECT' function only seems to work when the other documents (ce.xls and df.xls) is open.
Who can help ?
Chris


Posted by Damon Ostrander on August 26, 2001 5:58 PM

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

Posted by chris on August 27, 2001 1:17 AM

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

Posted by Ivan F Moala on August 27, 2001 2:58 AM

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,