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

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.

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

you can do this via a standard INDIRECT.
BUT you'll have to ensure the workbooks are open.

laurent longre has a free addin MOREFUNC
which includes a functions INDIRECT.EXT
that circumvents the problem.

if you need distribute the workbook, users must have the addin.

d/l from http://xcell05.free.fr/





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wattkisson wrote :

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.

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

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

"keepITcool" wrote:


you can do this via a standard INDIRECT.
BUT you'll have to ensure the workbooks are open.

laurent longre has a free addin MOREFUNC
which includes a functions INDIRECT.EXT
that circumvents the problem.

if you need distribute the workbook, users must have the addin.

d/l from http://xcell05.free.fr/





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wattkisson wrote :

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.