Forum Discussion
How to reference a cell in another workbook without storing absolute path to source file
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$1and 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.
SergeiBaklan 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?
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$1and 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.