Linking to Another Excel File (File Name Changes)

[PeterNEA]

Hi,

I have 2 separate Excel file worksheets. Basically, one file is
updated each month and will have a different name (example: File052007
for May, File062007 for June, File072007 for July, etc.). Currently,
I'm trying to link cells in my other file to the updated file, but I
don't know how to set the link so that it can change each month. I was
trying the concatenate or (A1&A2&A3) options to try to link cells
together, but haven't had much luck.

Here is what the formula should look like:

='C:\FOLDER\[File052007.xls]Sheet1'!$A$1

I made 2 cells in this worksheet so that one displays the current
month (05) and the current year (2007). So I was trying to somehow
concatenate the 2 cells into the formula so it will change each month.
Kind of like this:

='C:\FOLDER\[File(Here is where I would put the 2 cells).xls]Sheet1'!$A
$1

But it doesn't seem to be working. Also, I was told the INDIRECT
option can work in this case, but I don't know how to use it. If
anyone could help, I would greatly appreciate it.

Thanks!

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Hi,

I have 2 separate Excel file worksheets. Basically, one file is
updated each month and will have a different name (example: File052007
for May, File062007 for June, File072007 for July, etc.). Currently,
I'm trying to link cells in my other file to the updated file, but I
don't know how to set the link so that it can change each month. I was
trying the concatenate or (A1&A2&A3) options to try to link cells
together, but haven't had much luck.

Here is what the formula should look like:

='C:\FOLDER\[File052007.xls]Sheet1'!$A$1

I made 2 cells in this worksheet so that one displays the current
month (05) and the current year (2007). So I was trying to somehow
concatenate the 2 cells into the formula so it will change each month.
Kind of like this:

='C:\FOLDER\[File(Here is where I would put the 2 cells).xls]Sheet1'!$A
$1

But it doesn't seem to be working. Also, I was told the INDIRECT
option can work in this case, but I don't know how to use it. If
anyone could help, I would greatly appreciate it.

Thanks!

 

[Guest]

To do the concatenation just put the whole thing inside an Indirect and use "
marks and & as needed (note carefully the " and ' in the right places) eg:
=INDIRECT("'C:\FOLDER\[File" & CellContainingMonth & CellContainingYear &
".xls]Sheet1'!$A$1)

As mentioned, this only works when the source file is open