<div>
Hello JameMeck,<br />
<br />
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.<br />
<br />
The formula would look like this<br />
<br />
=Indirect(&quot;'4 Apr-10\[Apr-10.xls]&quot;&amp;K2&amp;&quot;'<wbr />!$W$38&quot;)<br />
<br />
Please note the type and order of the quote marks in the formula.<br />
<br />
cheers, teylyn
</div>


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

<div>
Thank you very much!<br />
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.<br />
Do you have any solution?
</div>


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?

<div>
<div class="content wysiwyg-content">
I have 1 excel data file with 31 sheets, and 1 report excel file.<br />
The report excel file links to 31 sheets (days) of the excel data file, and I have formular like this:<br />
<br />
The report excel file:<br />
K2(Date): 12--&gt; I will change this value everyday.<br />
A5 (linked data from each sheet of the report excel file): =='4 Apr-10\[Apr-10.xls]12'!$W$<wbr />38<br />
<br />
so I want: when I change the value of K2 (Date) to 13, the link of A5 will change automatically to:<br />
'4 Apr-10\[Apr-10.xls]13'!$W$<wbr />38<br />
<br />
Please help me.
</div>
</div>


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.

Excel 2007 Dynamic Link To Another Files.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.