Hi, I want to change the path to a file in excel link but I always get an error.
='\\mylocation\folder1\folder2\[file001.001]Sheet1'!$A$1

I want to be able to change the file name and the folder name, I tried to do something like this but I always get an error.

='\\mylocation\folder1\folder' & A1 & '2\[file' & B1 & '.' & B1 & ']Sheet1'!$A$1

I also tried different string manipulations but always get an error or excel won't assume it as a formula.

I even tried to create a function but it also fails if I call it from the worksheet.

Any ideas

Thank you.

Hi,
Excel considers this to be a string and not a range reference. There's a worksheet function called INDIRECT() which can be used to convert a string to a range reference. Unfortunately there are two drawbacks to using INDIRECT():

1. It's a volatile function.
2. If it's pointing at an external workbook which is closed, it returns an error value.

Point 2 is the big factor for your question because you are trying to reference an external workbook.



With INDIRECT() put to one side, your options are either to write (or borrow) some VBA code or to install an add-in which has the required functionality. Some choices are discussed on this thread at Daily Dose Of Excel including:

• MOREFUNC.XLL which includes the INDIRECT.EXT function
• Harlan Grove's PULL function

Hope that helps...

Going to take a look to those threads.
Still working to find a way around.
Thank you for your input.