Sharing Multiple linked Spreadsheets with Multiple users using Google Drive

[m1h3sh]

Use case:

I work for a small business where we use office365 apps especially Microsoft Excel. We have a spreadsheet "Dashboard" which uses data from multiple spreadsheets. Fortunately all of the users use Windows OS so I was able to setup Google Drive folder for all computers with same folder structure so that the connected links won't break even if someone opens Dashboard in any computer which has google drive setup. 

This is our folder structure: 

C:/Google Drive/Dashboard/Dashboard.xlsx

Challenge: 

One of the users recently purchased a MacBook as a personal preference and I have setup the Google Drive on the MacBook but when I open the Dashboard all the links are broken as the new folder structure path is as follows 

/Users/userName/Google Drive/Dashboard/Dashboard.xlsx

If I change the links on the MacBook, the dashboard on the windows computers breaks and vice versa. 

I was wondering if there is any way that I can fix the issue

[#2]

@m1h3sh 

I'm not a Mac specialist, not even a googel drive user.

Prefer OneDrive, safer and works better with Office, a lot of security for little money .. I can only recommend it :).
But here is some information about it that I found in the Microsoft world.

Info:

Problem opening files or folders via hyperlinks in Excel 2016 for Mac

https://support.microsoft.com/en-us/office/problem-opening-files-or-folders-via-hyperlinks-in-excel-...

Last Updated: March 6, 2018

ISSUE

When you click a hyperlink to open a file or folder, it may not open a Finder window to show the folder or it may not open the linked file.

Example: Insert the following hyperlink in any cell file:///Users/<your username>/Desktop. Next, click on the hyperlink you just created. The hyperlink should open a Finder window but it does not.

STATUS: FIXED

This issue has been fixed in version 16.11.0 or higher. To get the latest update, open Excel for Mac, go to the Help > Check for Updates menu, and follow the instructions in the AutoUpdate app.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

@NikolinoDE 

Thank you for your response. I read the blogpost response you attached but the answer I was looking for "Is there any way relative paths are achieved in excel?". I am not sure why excel team chosen to use base path as "C:/username/folder1/folder2/file.xlsx" instead "./../../folder1/folder2/file.xlsx" approach which makes all links cross platform compatible. 

[#4]

@m1h3sh 

Causes of broken hyperlinks in the cloud
The links change as a result of the copying process.
According to the translation, you have this problem.
If this is correct, here is some information that I was able to find.

This means that my knowing (not knowing) is at the end of the matter.

The result:

the links don't work because the files relatively referenced by Excel are not in the cloud.

In order for the hyperlinks to continue to work, you would have to take over all files and the folder structure online.

Microsoft Excel 2016 - cloud storage bends local link paths

Save absolute paths
It is easier if you get Excel to save absolute paths instead of the relative paths. Y

ou have to do this before you copy the Excel file.

To do this, open the file and navigate to the Backstage view via the file tab.

Now switch to Information / Properties / Advanced Properties.

On the Summary tab, enter a point for the current path in the Link base field.

Now confirm with the OK button - done.

Problems working with absolute paths
When you then save the file, Excel uses the absolute path. However, this setting only applies per file, not globally, so you have to repeat the process for each document.

If the link still cannot be accessed online, the usual suspects such as firewalls and other security mechanisms are not the only options. Rather, the respective cloud provider can also prevent certain hyperlinks or document types from being accessed at any time.

Additional infos:

Mac Excel 2019 hyperlinks (relative path), plain text display and validity check (is in German)

https://www.macuser.de/threads/mac-excel-2019-hyperlinks-pfad-relativ-klartextdarstellung-und-guelti...

Description of link management and storage in Excel

https://support.microsoft.com/en-us/help/328440/description-of-link-management-and-storage-in-excel

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)

[#5]

@m1h3sh 

That's bit strange. If all files linked to dashboard are on the root of GoogleDrive and Dashobard is next folder, it shall work. Actually Excel keeps relative part and adds to it absolute part if you move files on another computer. What is important the way from one file to another through such relative part shall be the same on all computers.

Please check Description of link management and storage in Excel