Manage external data source locations in Excel when sharing the file across Dropbox

You talk about external data sources, main file, and data source file. Can you be more specific about which file references which other file and how? You have posted in the VBA subforum, so are these references in the VBA code? Or are they in formulas? Where are these two files stored relative to each other?

I use one file that has formulas with external links to another file. Generally in Excel, formulas with external links are relative links. However, the implementation is confusing because Excel will show the full path name. For example, I have one file called Scratch that is located at this path:

'C:\Users\Jeff\Dropbox\Management\Labor reports\Scratch.xlsx'

This file contains a formula which contains a link to another file in the same folder. The formula looks like this:

='C:\Users\Jeff\Dropbox\Management\Labor reports\[Actuals Latest.xlsm]Rates'!$J$8

However, contrary to appearances, this is a relative link. If I close the files and copy these same two files to another folder, now the formula looks like this:

='C:\Users\Jeff\Documents\scratch\[Actuals Latest.xlsm]Rates'!$J$8

Excel has automatically updated the formula to refer to the same relative location.

I take advantage of this to share files between two machines using Dropbox.

Therefore, if all your users store the main file and data source file in the same folder, then the external references will work no matter what folder it is, no matter what machine is it. It doesn't even have to be the same folder, as long as the two folders are the same relative to each other. In your case they should both be under the Dropbox structure.

If all of this is in VBA then the story is a little different. In VBA the user's main folder can be referenced as

Please Login or Register  to view this content.

In my case this returns string "C:\Users\Jeff". The standard location for the Dropbox folder is immediately under this so you could use

Please Login or Register  to view this content.

I use the default. However, Dropbox allows the user to select a custom location. This is a power-user option and not many people do it, but if they do, this becomes more difficult. Dropbox does not set a Windows environment variable for this, and I'm not sure how to find it. .

please read the forum rules, especialy the ones about crossposting.

https://www.mrexcel.com/forum/excel-...s-dropbox.html