How do I use a cell value as the filename in an external link?

W

wattkisson

I want to set up a workbook with several values from linked workbooks. I
would like to enter a value in column A and then use that value as the
filename in the rest of the cells that link to the external workbook.
For example:

I want to enter A122 into colum A - and have the cell in column B pick that
value up and link to a cell in an external workbook with that name

Obviously, the following function links correctly to the external file and
returns the correct value:
='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2

But, I do not want to have to change the syntax in a large number of cells
everytime I add to the list. I only want to enter the value (A122) in the
first cell and have the others retrieve linked values correctly.
In other words, I want a dynamic, external link.

Thanx in advance for any help.
 
D

Dave Peterson

You'd want to use the =indirect() worksheet function. But that doesn't work
with closed files.

Harlan Grove wrote a UDF called that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
I want to set up a workbook with several values from linked workbooks. I
would like to enter a value in column A and then use that value as the
filename in the rest of the cells that link to the external workbook.
For example:

I want to enter A122 into colum A - and have the cell in column B pick that
value up and link to a cell in an external workbook with that name

Obviously, the following function links correctly to the external file and
returns the correct value:
='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2

But, I do not want to have to change the syntax in a large number of cells
everytime I add to the list. I only want to enter the value (A122) in the
first cell and have the others retrieve linked values correctly.
In other words, I want a dynamic, external link.

Thanx in advance for any help.
Click to expand...
 
E

Earl Kiosterud

Wattkisson,

You could use the INDIRECT function, but the linked workbook must be open.
Other than that, I think your only solution will be to create the formula
with a macro.

Often a project is split into separate workbooks (and separate worksheets),
when it should be in fewer (or one) workbook. Consider that carefully; it
might be what you need.
 
K

Keyur

following is a code i am using in one of my spreadsheet to do similar
thing. the only difference might the fact that i am retreiving same
cell (A3) form each file.


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim flnm As String
Application.EnableEvents = False
If Target.Column = 1 And IsEmpty(Target.Value) = False Then
flnm = "C:\Documents and Settings\kapatel\Desktop\" & Target.Value & ".xls"
If Dir(flnm) <> "" Then
Me.Cells(Target.Row, 2).Formula = "='C:\Documents and Settings\kapatel\Desktop\[" & Target.Value & ".xls]Details'!$A$3"
Else
Me.Cells(Target.Row, 2).Value = "File not found."
End If
ElseIf IsEmpty(Target.Value) = True Then
Me.Cells(Target.Row, 2).Value = "File not found."
End If
Application.EnableEvents = True
End Sub

--------------------


Hope this helped

Keyur
 
W

wattkisson

Thanks. INDIRECT did the trick. Now I will try to get around the open
workbook updating issues as you suggested.

Dave Peterson said:
You'd want to use the =indirect() worksheet function. But that doesn't work
with closed files.

Harlan Grove wrote a UDF called that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
I want to set up a workbook with several values from linked workbooks. I
would like to enter a value in column A and then use that value as the
filename in the rest of the cells that link to the external workbook.
For example:

I want to enter A122 into colum A - and have the cell in column B pick that
value up and link to a cell in an external workbook with that name

Obviously, the following function links correctly to the external file and
returns the correct value:
='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2

But, I do not want to have to change the syntax in a large number of cells
everytime I add to the list. I only want to enter the value (A122) in the
first cell and have the others retrieve linked values correctly.
In other words, I want a dynamic, external link.

Thanx in advance for any help.
Click to expand...
Click to expand...
 
Top