Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Approach Linked documents via INDIRECT
I try to approach an external document, defined in cell A1 in cell B2
A1 := '[File1.xls]Sheet1!$A$1 A2 := =INDIRECT(A1) This works perfectly if File1.xls is open, but gives #REF! is File1.xls isn't open. Is there a way I can link to a file defined in a cell, without that file being opened (in the real problem I link to a file with a specific element in the name that changes frequently. I want to define the exact file to link to in the "master" excel sheet). Thanks, Johannes |
#2
|
|||
|
|||
Johannes wrote:
A2 := =INDIRECT(A1) This works perfectly if File1.xls is open, but gives #REF! is File1.xls isn't open. Correct. That is a feature of INDIRECT Is there a way I can link to a file defined in a cell, without that file being opened Not without running a macro to change the link source(s) for you. For example you could have a Worksheet_Change procedure that detects a change in A1 and changes the link source to that file. Something like: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub If IsEmpty(Me.Range("A1")) Then Exit Sub If Dir(Me.Range("A1"))="" Then MsgBox "File " & Me.Range("A1") & " not found" Exit Sub End If ThisWorkbook.ChangeLink ThisWorkbook.LinkSources(xlExcelLinks)(1), Me.Range("A1") End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
Thanks Bill,
Does not happen too often, but every now and then i am disappointed by Excel. Thanks for the work-around though. Johannes "Bill Manville" schreef in bericht ... Johannes wrote: A2 := =INDIRECT(A1) This works perfectly if File1.xls is open, but gives #REF! is File1.xls isn't open. Correct. That is a feature of INDIRECT Is there a way I can link to a file defined in a cell, without that file being opened Not without running a macro to change the link source(s) for you. For example you could have a Worksheet_Change procedure that detects a change in A1 and changes the link source to that file. Something like: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub If IsEmpty(Me.Range("A1")) Then Exit Sub If Dir(Me.Range("A1"))="" Then MsgBox "File " & Me.Range("A1") & " not found" Exit Sub End If ThisWorkbook.ChangeLink ThisWorkbook.LinkSources(xlExcelLinks)(1), Me.Range("A1") End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove empty chart plots from linked worksheet charts | Charts and Charting in Excel | |||
create space in line chart between points, linked to pivot table | Charts and Charting in Excel |