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

Question

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

User · Accepted Answer

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

User · Answer

I had a similar problem that I solved by using the following sequence    use the CELL  filename   function to get the full path to the current sheet of the current file  use the SEARCH   function to find the start of the  FileName SheetName string of your current excel file and the sheet  use the LEFT function to extract the full path name of the directory that contains your current file   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  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      amp  LEFT CELL  filename   SEARCH   MyFileName MySheetName  CELL  filename    - 1   amp       amp   OtherFileName   amp       amp   OtherSheetName   amp        amp    OtherColumn OtherRow   amp         Note that LibreOffice uses a slightly different CellPatnName syntax  as in the following example    INDIRECT LEFT CELL  filename   SEARCH   MyFileName MySheetName  CELL  filename    - 1   amp   OtherFileName   amp         amp   OtherSheetName   amp       amp    OtherColumn OtherRow

User · Answer

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

User · Answer

What works for me was with one dot   Mine Office is 365   HYPERLINK    Name of folder    DisplayLinkName    HYPERLINK    Name of file   DisplayLinkName

User · Answer

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 --------------       amp C2 amp C3 amp     amp C3 amp   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

User · Answer

easier  amp  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

User · Answer

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

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

Examples related to reference

Examples related to excel