VLOOKUP File Path

[itipu]

hello, I am somewhat stuck.. I have a
VLOOKUP='C:\DATA\J72097\A\Calculations\Rates_R3.xls'!SURV_RATE

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

=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)

works fine... I then can do

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

'C:\DATA\J72097\A\Calculations\Rates_R3.xls

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!

[BrianMH]

The indirect function will probably help you. An example can be found at http://www.mrexcel.com/forum/showthread.php?t=64498

[itipu]

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...

VLOOKUP='C:\DATA\J72097\A\Calculations\Rates_R3.xls'!SURV_RATE

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.