Forum Discussion
Update Excel formulas between a path in local folder and owncloud in another computer
I have a lot of Excel archives with lots of formulas which reference other Excel files, all in the path H:\Fold\Data\physics on a computer, and at the same time, on another computer, I have an owncloud folder C:\Users\fulanito\ownCloud\Shared\Data\physics.
How could I change the paths in formulas to be able to update the data when I open them in the owncloud folder or in the original folder? They share part of the same route: \Data\physics
The local folder on one computer (H:\Fold\Data\physics) is synchronized with owncloud, I share the folder Data.
Mounted folder in owncloud in another computer: C:\Users\fulanito\ownCloud\Shared\Data\physics
I tried to use relative paths but that works only if all archives are in the same folder. In my case for the owncloud path, for example
- one archive, data_2025.xlsx is in C:\Users\fulanito\ownCloud\Shared\Data\physics\2025
- another, data_2024.xlsx, is in C:\Users\fulanito\ownCloud\Shared\Data\physics\2024
- the Excel that use formulas with references to these two archives is in C:\Users\fulanito\ownCloud\Shared\Data\physics\summary.
So if I want to update the summary archive daily_summary on the computer with owncloud, all the archives have to be in the same subfolder \summary\
Examples of formulas I use in the summary sheet to reference my files in H, in Excel daily_summary.xlsx are
='H:\Fold\Data\physics\2025\[data_2025.xlsx]Horario'!AV1419
and in another cell next to this is:
='H:\Fold\Data\physics\2025\[data_2024.xlsx]Horario'!AV1419
So if a make a change in data_2025 in that cell and I open on the computer with owncloud, it can't update because it references the local computer H:. I prefer not to use macros.
Map the same drive letter on both computers.
That’s the cleanest, no-macro, no-maintenance solution:
- Excel thinks it’s opening the same files.
- You can work seamlessly both locally and in OwnCloud.
- No need to rewrite formulas.
Example
On both computers, ensure this path works:
H:\Fold\Data\physics\summary\daily_summary.xlsx
On the OwnCloud computer, that’s just a mapped shortcut to:
C:\Users\fulanito\ownCloud\Shared\Data\physics\summary\daily_summary.xlsx
Then formulas like:
='H:\Fold\Data\physics\2025\[data_2025.xlsx]Horario'!AV1419
will update perfectly from either machine.
My answers are voluntary and without guarantee!
Hope this will help you.
3 Replies
Wow. That was a very correct and simple solution. Thank you. If the fold is a C: I only have to create the "fake" fold of the original formula and then use
mklink /J "C:\Fold\Data\physics\" "C:\Users\fulanito\ownCloud\Shared\Data\physics\"
So they always will be linked and excel will find C:\Fold
But in this case is not a C partition, is H: so I have to use an empty fold like
C:\testH and then subst H: "C:\testH"
Because subst have to be used every time I iniate session I created a little script that initiate every time I turn on my computer. So, problem solved. Thank you very much for your simple answer.
Now excel thinks H: exists in the mounted computer doesn't have to look for routes.Map the same drive letter on both computers.
That’s the cleanest, no-macro, no-maintenance solution:
- Excel thinks it’s opening the same files.
- You can work seamlessly both locally and in OwnCloud.
- No need to rewrite formulas.
Example
On both computers, ensure this path works:
H:\Fold\Data\physics\summary\daily_summary.xlsx
On the OwnCloud computer, that’s just a mapped shortcut to:
C:\Users\fulanito\ownCloud\Shared\Data\physics\summary\daily_summary.xlsx
Then formulas like:
='H:\Fold\Data\physics\2025\[data_2025.xlsx]Horario'!AV1419
will update perfectly from either machine.
My answers are voluntary and without guarantee!
Hope this will help you.
Wow. That was a very correct and simple solution. Thank you. If the fold is a C: I only have to create the "fake" fold of the original formula and then use
mklink /J "C:\Fold\Data\physics\" "C:\Users\fulanito\ownCloud\Shared\Data\physics\"
So they always will be linked and excel will find C:\Fold
But in this case is not a C partition, is H: so I have to use an empty fold like
C:\testH and then subst H: "C:\testH"
Because subst have to be used every time I iniate session I created a little script that initiate every time I turn on my computer. So, problem solved. Thank you very much for your simple answer.
Now excel thinks H: exists in the mounted computer doesn't have to look for routes.
