If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel - Vlookup and links to another workbook
Hi All,
Source file - receive everyday and it named as 01.24.04abc.xls, 01.25.04abc.xls,01.26.04abc.xls and so on Lookup File - Date Lookup Value 01/24/04 =vlookup(a2,'path\01.24.04abc\tab\Range,4,0) 01/25/04 =vlookup(a3,'path\01.25.04abc\tab\Range,4,0) 01/26/04 =vlookup(a4,'path\01.26.04abc\tab\Range,4,0) What I have been doing is going into the lookup up file and do find and replace filename on cells where I have the vlookup formula everyday. Is there a way to make the filename dynamic so that it's looking into the value in Column A and know which file to get info from? Any help will be greatly appreicated. Thanks, Chuck --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Excel - Vlookup and links to another workbook
Hi Chuck
with the assumption that your source file is also opened you can use the following =vlookup(a2,INDIRECT("'path\" & MONTH(A2) & "." & DAY(A2) & "." & RIGHT(YEAR(A2),2) & "abc\tab\Range"),4,0) Note: INDIRECT won' work if the other file is closed. HTH Frank Hi All, Source file - receive everyday and it named as 01.24.04abc.xls, 01.25.04abc.xls,01.26.04abc.xls and so on Lookup File - Date Lookup Value 01/24/04 =vlookup(a2,'path\01.24.04abc\tab\Range,4,0) 01/25/04 =vlookup(a3,'path\01.25.04abc\tab\Range,4,0) 01/26/04 =vlookup(a4,'path\01.26.04abc\tab\Range,4,0) What I have been doing is going into the lookup up file and do find and replace filename on cells where I have the vlookup formula everyday. Is there a way to make the filename dynamic so that it's looking into the value in Column A and know which file to get info from? Any help will be greatly appreicated. Thanks, Chuck --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Excel - Vlookup and links to another workbook
Hi
unfortunately in your example: No. Though there is a free add-in to overcome the restriction of INDIRECT for closed files (see MOREFUNC.XLL available at http://longre.free.fr/english - function INDIRECT.EXT) this won't work in your combination with VLOOKUP. One way around this: 1. Create a new (hidden) sheet in your main workbook on which you copy the data from your source file using ONLY the function INDIRECT.EXT 2. Reference with VLOOKUP on this sheet HTH Frank Thanks Frank. For formula works perfectly . but is there a way where I don't need to have the source files opened? |
#4
|
|||
|
|||
Excel - Vlookup and links to another workbook
Harlan Grove wrote:
"Frank Kabel" wrote... unfortunately in your example: No. Though there is a free add-in to overcome the restriction of INDIRECT for closed files (see MOREFUNC.XLL available at http://longre.free.fr/english - function INDIRECT.EXT) this won't work in your combination with VLOOKUP. . . .. Correct with respect to INDIRECT.EXT - looks like it can return only single values. Incorrect with respect to that being the only alternative that wouldn't require additional formulas in ancillary cells. See the third alternative in the following. http://www.google.com/groups?selm=zK...40www.newsrang er.com Hi Harlan sorry I forgot to mention this alternative though I tried it some time ago. But (as you mentioned in your original post) this alternative is very slow. Especially then referencing a large range in the closed file. So the OP has to test for himself if this fits his needs. (Maybe sometime Microsoft will extend INDIRECT...) Regards Frank |
Thread Tools | |
Display Modes | |
|
|