File Syncing in Dropbox

Within our company we use a number of Excel workbooks that link to other Excel workbooks.

We use Dropbox and have run into issues when opening the same file on different computers - the files being linked to the file return with "#VALUE!" in each cell. With all of the services we have tested the file path for files is different for every machine. For example the same folder on two different machines would have file locations as below:

Machine 1 Dropbox = C:[machine 1]\Dropbox\Excel Folder\Excel File

Machine 2 Dropbox = C:[machine 2]\Dropbox\Excel Folder\Excel File

When this happens you have to choose the source for the links every single time you open the file from a different machine. If you create a file with links to other files on machine 1 and then move to machine 2, machine 2 no knows where the files with 'machine 1' in the path as stored. This is a huge barrier for us using this kind of storage as every user would have to change each file each time they open it.

I understand why this happens, but is there a way to get around this and have files link together in such a way that the links are relative rather than absolute.

We are using Office 2016.

Re previous post, have just realised that ENVIRON is only a function in VBA. So to use the first option above, you would need to create a user defined function such as:

Please Login or Register  to view this content.

Your formula would then be:
=INDIRECT("C:\"&ENV("COMPUTERNAME")&"\Dropbox\Excel Folder\Excel File") - with of course the sheet name/rest of formula added within the quotes. You would also need files using these formulae to be macro enabled. This may make the drive mapping option the more straight forward!