Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Johannes
 
Posts: n/a
Default 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   Report Post  
Bill Manville
 
Posts: n/a
Default

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   Report Post  
Johannes
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I remove empty chart plots from linked worksheet charts Lee IT Charts and Charting in Excel 3 January 31st 05 05:31 PM
create space in line chart between points, linked to pivot table Mike -Z- Charts and Charting in Excel 1 December 7th 04 10:39 PM


All times are GMT +1. The time now is 10:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"