Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a formula dynamically. | Excel Worksheet Functions | |||
dynamically changing text color | Excel Worksheet Functions | |||
changing selections dynamically | Excel Discussion (Misc queries) | |||
Dynamically changing the size of a list | Excel Discussion (Misc queries) | |||
Changing Reference Dynamically | Excel Programming |