How to reference a cell in another workbook without storing absolute path to source file

[fjkattan]

Hello, whenever I reference a cell in another workbook, Excel stores the entire path to the other workbook even if the other workbook is in the same folder.

The absolute path to the source workbook is only valid in my own computer, but I need to be able to share my workbooks (source and destination) with someone else. I'd like to make it so as long as both workbooks are in the same folder, only the file name should be stored, not the entire path. 

I've managed to make a reference using:

='source.xlsx'!cell_name

To test, I make test copies of both source.xlsx and destination.xlsx in a new test folder. When I open the destination.xlsx file in the new test folder, i get a prompt to grant access to the source.xlsx file. Unfortunately after I give access, Excel changes the ='source.xlsx'!cell_name with a full path version:

='/users/msmith/Google Drive/testfolder/source.xlsx'!cell_name

Is there a way to configure Excel to save the links to external workbooks without storing the entire path name when both files are in th

[#2]

I think change links will work for what you are trying to do.

https://excelribbon.tips.net/T007364_Changing_Links.html

[#3]

@fjkattan 

Actually Excel works with relative links even if shows absolute path. But internally they are kept separately. If you move linked files to another location, Excel first checks relative part and if only another file is not found add to it absolute part.

More about that is here Description of link management and storage in Excel However, that is for Excel on Windows, I'm not if behavior is the same for Excel on Mac or not.

[#4]

@Sergei Baklan Thank you. This is helpful.

You wrote: "Actually Excel works with relative links even if shows absolute path"

The problem is I want to share a file without exposing the names of folders in my private hard disk.  For example, my destination workbook has a reference to:

='/Users/msmith/PrivateFolderName/source.clsx'!cell_name

If I email both workbooks to someone else, will the recipient see a reference to "PrivateFolderName" when he opens the destination workbook?  or will his version show a path that is specific to his private hard drive?

[#5 · Accepted solution]

@fjkattan 

Yes and no.

If on your machine the link in File1 on cell in File2 looks like

='D:\CompanyData\ConfidentialFolder\[File2.xlsx]Sheet1'!$A$1

, you email files to another person who copy/paste them into C:\OurPublicFolder, if File1 will be opened in read mode, first that another person will see is your link (if focus is on linked cell). Chances are high since Excel recognizes files are from outside, open it in read mode and asks you if you trust the source and would like to edit the file.

As soon as the person shifted on file edit mode, link will be transformed to

='C:\OurPublicFolder\[File2.xlsx]Sheet1'!$A$1

and your initial link will disappear forever.

Thus link works and your filepath is not visible after another person starts edit the file on his site.

Your initial link will be visible as well if Excel can't find File2. It simply alerts about broken link and continues to keep it.

If the main goal is to secure initial link afraid there is no straightforward way. You may unzip File1 and manually change root part of the path on something else, actually doesn't matter on what. Or first copy the files into temporary folder like c:\delivery, open and save them here and after that email outside. Not very suitable on practice, but security is quite often not suitable from time consuming and usability point of views. These are the only ways I know, perhaps some others exist as well.