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

I am producing a sheet to calculate prices. The sheet has to have a reference to several other workbooks to get the prices for different components. This works fine on my computer but when I move them to the server or another computer, it can't find the references.

My folder is structured like:

Folder
|-- prices.xlsx
|-- Fixed Components
|   |-- ComponentsA.xlsx
|   +-- ComponentsB.xlsx
|
+-- Variable Components
    |-- ComponentsC.xlsx
    +-- ComponentsD.xlsx

prices.xlsx is the main sheet that references the other sheets. On my computer it builds the references with an absolute path, so when I copy the files the path stays fixed to my machine, instead of referencing the the files on the other PC.

Is there any way to make the references relative so that I can put in the main sheet something like ='\Variable Components\[ComponentsC.xlsx]Sheet1'!A1. I would not want to use VBA for this as people don't tend to trust Macros and then complain the functions don't work.

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.

Presume you linking to a shared drive for example the S drive? If so, other people may have mapped the drive differently. You probably need to use the "official" drive name //euhkj002/forecasts/bla bla. Instead of S// in your link

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")

In Excel, there is a way to embed relative reference to file or directory. You can try type in excel cell : =HYPERLINK("..\Name_of_file_or_folder\","DisplayLinkName")

What works for me was with one dot. Mine Office is 365. =HYPERLINK(".\Name_of_folder\","DisplayLinkName") =HYPERLINK(".\Name_of_file","DisplayLinkName")