• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Excel 2007 Dynamic Link To Another Files.

I have 1 excel data file with 31 sheets, and 1 report excel file.
The report excel file links to 31 sheets (days) of the excel data file, and I have formular like this:

The report excel file:
K2(Date): 12--> I will change this value everyday.
A5 (linked data from each sheet of the report excel file): =='4 Apr-10\[Apr-10.xls]12'!$W$38

so I want: when I change the value of K2 (Date) to 13, the link of A5 will change automatically to:
'4 Apr-10\[Apr-10.xls]13'!$W$38

Please help me.
0
JameMeck
Asked:
JameMeck
  • 2
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello JameMeck,

You can do that with the Indirect() function, but for that to work you will need to have the Apr-10.xls workbook open. If the workbook is closed, the formula will return an error.

The formula would look like this

=Indirect("'4 Apr-10\[Apr-10.xls]"&K2&"'!$W$38")

Please note the type and order of the quote marks in the formula.

cheers, teylyn
0
 
JameMeckAuthor Commented:
Thank you very much!
But I have a lot of files to link data, it is nightmare to open all of those files to get data for my report.
Do you have any solution?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Yes. You would need to download and install a free Add-in called morefunc.xll
It includes a function called Indirect.Ext that can be used with closed workbooks, too.

The download is here http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

cheers, teylyn
0
 
JameMeckAuthor Commented:
Thanks, I got it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now