dynamic link
 

Is there a way to create a dynamic link?

For example if I had a list of Workbook names in column A, and a
repective list of links in column B. Is there a way to create one
formula that can be copied in Column B that would dynamically change
according to Column A?

Column A Column B
Legal01 =C:\My Documents\[Legal01.xls]Sheet1!$A$1
Legal02 =C:\My Documents\[Legal02.xls]Sheet1!$A$1
Legal03 =C:\My Documents\[Legal03.xls]Sheet1!$A$1

Itay wrote:
Is there a way to create a dynamic link?


Yes, but the source workbooks all have to be open to get the results
displayed.

=INDIRECT("'C:\My Documents\[" & A1 & "]Sheet1'!$A$1")

Usually it is more practical to create the specific formulas.

For example if columns B to CZ contain references to different cells in
the same workbook, copy them down from the first row and then select
the new row and Edit / Replace / Legal01 (with) Legal02 / Replace All

If there were very many rows it would be worth writing a macro to do it
for each of the rows. It might go something like this:

Sub SetUpFormulas()
' assumes data starts at A1, has header row and has filenames in
column A
' and row 2 already contains correct formulas
Dim I As Integer
With Range("A1").CurrentRegion
.Offset(1,1).Resize(.Rows.Count-1,.columns.count-1).FillDown
For I=3 To .Rows.Count
.Rows(I).Offset(,1).Replace Range("A2"), Range("A"&I),
lookAt:=xlPart
Next
End With
End Sub



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup