Use a cell value to specify the path address

[BNCOXUK]

Dear all,

I have a few files to link with. I want to achieve that: if I specify the file name in cell A1, then the file to be linked will also be updated with the path change. For example,

If I specify the file name as SourceFile in cell A1, then the target path is: ='C:\[SourceFile.xlsx]Sheet1'!$A$1

Not sure if I need to use text functions to achieve this. Thank you for help!

[Ace_XL]

This will display the text for the target path

="'C:\["&A1&".xlsx]Sheet1'!$A$1"

If you need to pull information from the target file you'll have to use the INDIRECT.EXT function
http://www.ashishmathur.com/tag/indirect-ext/

[BNCOXUK]

Many thanks, Ace_XL. Interestingly, there is no direct way to do this. Only have to use the adds-on function