Connection between excel files in a dropbox

Good afternoon everyone, I have a question about how to connect two Excel files within a Dropbox in a company.

Situation:

There are several files that connect to a main spreadsheet and pull data. Each file is in a different folder within dropbox. To make the question easier to understand, I will use 2 files as a test:

I have two excel files where one pulls information from the other, for example, I have an excel file "test 1" that pulls information from an excel file "test 2" and after that the test 2 file uses information from the results of test 1, or that is, a double connection. Therefore, the moment I reference a cell to another file, the formula bar appears for example:

"='C:\Users\rafae\dropbox\...\[test 2.xlsx]Plan1'!$G$5"

However, this link is only functional for the user "rafae", the connection only works if the user "rafae" has both spreadsheets open. If a second user "joao" opens the test 2 spreadsheet and updates the values, the test 1 spreadsheet will not update, as the link will only be for the "rafae" user. If another dropbox user wants to update the values, they must update the links to the person's own path:



Is there any way to connect these different files that link to a main file and return values, in a way that works for all users? We don't want to have to update the links again every time we present something so someone else can update the values ​​live.

Is it possible to do this without using VBA? We don't want to go to that level for a simple connection between spreadsheets. If this is not possible, what would be the ways to resolve this issue? Macros, VBA?


Thanks!Screenshot_2.png