Dynamic Link

CPGUY33

New Member
Joined
Sep 27, 2006
Messages
9
How can I create a dynamic link to another excel file? I want the user to type in a name in cell A1 and in cell B1 it is linked to the file name typed in A1 and reads a cell value in that file. Can this be done through a function such as =[name.xls]Sheet1!C20 where "name" is what was typed in cell A1?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
How can I create a dynamic link to another excel file? I want the user to type in a name in cell A1 and in cell B1 it is linked to the file name typed in A1 and reads a cell value in that file. Can this be done through a function such as =[name.xls]Sheet1!C20 where "name" is what was typed in cell A1?

If you can keep the target file open...

=INDIRECT("'"&A1&"Sheet1!C20")

Otherwise, download and install the morefunc.xll add-in and invoke:

=INDIRECT.EXT("'"&A1&"Sheet1!C20")
 

CPGUY33

New Member
Joined
Sep 27, 2006
Messages
9
Thanks it works great after a slight modification and downloading the morefunc.xll add-in:
=INDIRECT.EXT("'["&A1&".xls]Sheet1'!c20")

I have found that when you first type in this equation to a new sheet it gives #VALUE!. You must save and close, then reopen the sheet for the corect value/link to show up. Is this how it is supposed to work?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Thanks it works great after a slight modification and downloading the morefunc.xll add-in:
=INDIRECT.EXT("'["&A1&".xls]Sheet1'!c20")

I have found that when you first type in this equation to a new sheet it gives #VALUE!. You must save and close, then reopen the sheet for the corect value/link to show up. Is this how it is supposed to work?

This behavior is not mentioned in Morefunc's Help. If you want to, you can post the question at:

http://xcell05.free.fr/forums/index.php
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top