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.