How do I make a relative reference to another workbook in Excel?

The only solutions that I've seen to organize the external files into sub-folders has required the use of VBA to resolve a full path to the external file in the formulas. Here is a link to a site with several examples others have used:

http://www.teachexcel.com/excel-help/excel-how-to.php?i=415651

Alternatively, if you can place all of the files in the same folder instead of dividing them into sub-folders, then Excel will resolve the external references without requiring the use of VBA even if you move the files to a network location. Your formulas then become simply ='[ComponentsC.xlsx]Sheet1'!A1 with no folder names to traverse.

I had a similar problem that I solved by using the following sequence:

1. use the CELL("filename") function to get the full path to the current sheet of the current file.

2. use the SEARCH() function to find the start of the [FileName]SheetName string of your current excel file and the sheet.

3. use the LEFT function to extract the full path name of the directory that contains your current file.

4. Concatenate the directory path name found in step #3 with the name of the file, the name of the worksheet, and the cell reference that you want to access.

5. use the INDIRECT() function to access the CellPathName that you created in step #4.

Note: these same steps can also be used to access cells in files whose names are created dynamically. In step #4, use a text string that is dynamically created from the contents of cells, the current date or time, etc. etc.

A cell reference example (with each piece assembled separately) that includes all of these steps is:

=INDIRECT("'" & LEFT(CELL("filename"),SEARCH("[MyFileName]MySheetName",CELL("filename")) - 1) & "[" & "OtherFileName" & "]" & "OtherSheetName" & "'!" & "$OtherColumn$OtherRow" & "'")

Note that LibreOffice uses a slightly different CellPatnName syntax, as in the following example:

=INDIRECT(LEFT(CELL("filename"),SEARCH("[MyFileName]MySheetName",CELL("filename")) - 1) & "OtherFileName" & "'#$" & "OtherSheetName" & "." & "$OtherColumn$OtherRow")

easier & shorter via indirect: INDIRECT("'..\..\..\..\Supply\SU\SU.ods'#$Data.$A$2:$AC$200")

however indirect() has performance drawbacks if lot of links in workbook

I miss construct like: ['../Data.ods']#Sheet1.A1 in LibreOffice. The intention is here: if I create a bunch of master workbooks and depending report workbooks in limited subtree of directories in source file system, I can zip whole directory subtree with complete package of workbooks and send it to other cooperating person per Email or so. It will be saved in some other absolute pazth on target system, but linkage works again in new absolute path because it was coded relatively to subtree root.

Using =worksheetname() and =Indirect() function, and naming the worksheets in the parent Excel file with the name of the externally referenced Excel file. Each externally referenced excel file were in their own folders with same name. These sub-folders were only to create more clarity.

---

What I did was as follows:-

|----Column B---------------|----Column C------------|

R2) Parent folder --------> "C:\TEMP\Excel\"

R3) Sub folder name ---> =worksheetname()

R5) Full path --------------> ="'"&C2&C3&"["&C3&".xlsx]Sheet1'!$A$1"

R7) Indirect function-----> =INDIRECT(C5,TRUE)

In the main file, I had say, 5 worksheets labeled as Ext-1, Ext-2, Ext-3, Ext-4, Ext-5. Copy pasted the above formulas into all the five worksheets. Opened all the respectively named Excel files in the background. For some reason the results were not automatically computing, hence had to force a change by editing any cell. Volla, the value in cell A1 of each externally referenced Excel file were in the Main file.