View Full Version : VLOOKUP File Path

11-16-2011, 08:43 AM
hello, I am somewhat stuck.. I have a

works fine as long as the path is there... since files move.. I thought of replacing path with another formula to calculate existing path... so


works fine... I then can do

=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1) & "Rates_R3.xls" this returns


greta... but I am unable to insert it back into VLOOKUP... i.e. something like this but that would work

VLOOKUP='(LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1) & "Rates_R3.xls)'!SURV_RATE

hmmm any help much appreciated!

11-16-2011, 09:13 AM
The indirect function will probably help you. An example can be found at http://www.mrexcel.com/forum/showthread.php?t=64498

11-16-2011, 11:07 AM
Point taken, but this really isn't what I want... I just want a formula which will use the full path of the opened workbook and reference other files within the same folder...


code above works for generating text in red... I was just thinking it is the matter of syntax to insert text into the VLOOKUP above... perhaps I am wrong.