I have 2 excel files. One is the source file and the other is the final file. These 2 files reside on different folders. But the 2 folders are under the same mother folder.

To illustrate:
FileFolder/SourceFolder/source.xls
FileFolder/FinalFolder/final.xls

I will need to link several cells of the final file to the source file. So Simply, i just typed in "=" then went to the source file and pointed to the cell i will be linking with. Done! But the problem is..the FileFolder (including its subfolders and all the files under these subfolders) might be copied to different file locations or path, when i did this..the link is gone.

How do I get the current path then connect it to the filename of the final file plus the cell(row,column) of the source file where i will be linking with? The constant here are the filename and cell(row,column) of the source file. Seemed easy, I keep on testing but I cant get it right. I was able to place/put the "current path + source file name + sheet name of the source" on 1 cell location in the final file. Then i used "&" to add the cell location.

Ex. 'C:\FileFolder2\SourceFile\[source.xls]Sheet1'!$A$2

But I can't get the value. Pls. help. Sorry for the long explanation.

The only native way to construct a range reference from a string in a formula is by using INDIRECT(). However, INDIRECT() will only work when the other workbook is open; if it is closed then it will return an error.

Without re-inventing the wheel, you could go to Edit | Links | Update values and browse to the correct file. Or perhaps you could give Jan Karel Pieterse's link fixing utility a try? This shouldn't happen very often, right?

I can't consider that option of Edit|Links|Update coz there will be a weekly update on the source file. Thanks for the reply.

Hi,
Why does that mean that Edit|Links|Update isn't an option?

I guess we could automate this from the Workbook_Open event handler (ie. a VBA solution), if it's worth the effort? I'm not aware of any reason why this wouldn't be viable - but then again, I've never tried it. I expect that if you download that add-in the code will not be protected, so it would give you a head start on the objects and methods you're going to be needing to work with.

Or someone else might have a better suggestion!