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.

TimN wrote:

Hope this makes sense. I'm so confused, it's hard to even ask the
question... :)

I have a spreadsheet with location and file named S:\Employee\Active
1-1-07.xls. This is a list of all active employees. A new file is saved
every two weeks with the current employee list.

In a seperate spreadsheet user types in a date such as 12/18/06. There is a
VLOOKUP formula behind the scences that goes to the file above and finds a
specific employee as of 12/18/06. As you can see the problem is the current
file is for 1-7-07 not 12/18/06.

I want to dynamically change the file name to be the same as the one the
user types in then the VLOOKUP goes out to the appropriate file and does it's
thing.

I have used CONCATENATE to put together in one cell S:\Employee\Active and
the date the employee enters converted to format _12-18-06 to get that whole
string together in one cell. I want to take that and go to the table of data
where I have my VLOOKUP and point to this new Concatenanted value and say "go
to that file to look for the value". Can I do that?

In other words, I want the file name that the VLOOKUP goes to, to be driven
by the date the user enter.

Please HELP!!!

--

Dave Peterson