Dynamically changing a file name
 

With the date (12/18/06) in A1, this formula in B1
="[S:\Employer\Active"&MONTH(A1)&"-"&DAY(A1)&"-"&RIGHT(YEAR(A1),2)&".XLS]"
generates [S:\Employer\Active12-18-06.XLS]
Now if I use a formula such as =INDIRECT(B1&"Sheet1!B24")
I can get at the value in cell B4 of Sheet1 in that file
Of course on could use a single formula:
=INDIRECT("[S:\Employer\Active"&MONTH(A1)&"-"&DAY(A1)&"-"&RIGHT(YEAR(A1),2)&".XLS]"&"Sheet1!B24").
You did not show the VLOOKUP formula but I think you should be able to
modify it using this info
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"TimN" wrote in message
...
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!!!