itipu

11-16-2011, 08:43 AM

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!

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!