I'm importing data from .xls reports created each day. The file will
include the date.

Is it possible the file is called "Test 01-05-2008.xls", I could
create a string something like:

=[test +"A3"+ .xls]Sheet1!A1 // Where A3 is 01-05-2008

Help appreciated.

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.

========
Try opening "Test 01-05-2008.xls" first (easier to test).

Then in a worksheet in a different workbook, put the date in A1.

Then try this:
=INDIRECT("'[test " & TEXT(A1,"mm-dd-yyyy") & ".xls]sheet1'!A1")

(is 01-05-2008 January 5, 2008 or May 1, 2008???? Change that =text() format
the way you need).

After you have that working, you can try using =indirect.ext(). You'll have to
add the drive/path, so that it looks something like:

=INDIRECT.ext("'c:\excel\[test " & TEXT(A1,"mm-dd-yyyy") & ".xls]sheet1'!A1")

But that's a guess. I don't use that addin.

But lots of people do. If you have trouble getting it to work, post back with
the drive/folder name and what you've tried. I'll bet you get help!

AMaleThing wrote:

I'm importing data from .xls reports created each day. The file will
include the date.

Is it possible the file is called "Test 01-05-2008.xls", I could
create a string something like:

=[test +"A3"+ .xls]Sheet1!A1 // Where A3 is 01-05-2008

Help appreciated.

--

Dave Peterson

The indirect formula allows you to type in any value and use it as a cell (or
range?) reference. For example if you type =A1 into cell C1, whatever you
have typed into A1 will also be in cell C1. if you type ="A"&"1" in cell C1
you literally get "A1" in cell C1. If you enclose that formula in an indirect
statement =INDIRECT("A"&"1") you will once again get whatever was typed into
cell A1. Indirect changes the text "A1" into a cell reference. You can
substitue the value of a cell as a cell reference, for example D1="A1" and
cell C1=indirect(D1) then the value of C1 will be whatever is in A1.

"AMaleThing" wrote:

I'm importing data from .xls reports created each day. The file will
include the date.

Is it possible the file is called "Test 01-05-2008.xls", I could
create a string something like:

=[test +"A3"+ .xls]Sheet1!A1 // Where A3 is 01-05-2008

Help appreciated.