I've been working for a local business helping them set up their financial reports through Excel. However, we all use different computers/operating systems, but the files are synced through the cloud via Dropbox. So when updating the links, the address changes from something like C:/Users/fuschia/Dropbox/file.xlsx may change to /Library/Dropbox/file.xlsx and then to /fuschia/programs/Dropbox/file.xlsx
My original solution was to have a single user manage the file and create text copies of each file daily to reduce the strain on majority of users. However, that hasn't been working for my client.
Is there a way to store/link workbooks to work simply without the need to constantly edit links from each user? Does OneDrive provide this functionality?
If I need to explain something in more detail, I'll be glad to. Thanks in advance for any suggestions.
@fuschia If you switch to OneDrive, you'll be able to have multiple users collaborate in real-time. You can even have users in the desktop client version of Excel and Excel for the web at the same time.
@Smitty Smith Do users still work off the same "local" copy when collaborating?
I guess it's a bit easier for me to explain with a diagram and steps to recreate the issue:
User 1 updates Actual Revenues
User 1 updates Revenue Report
User 2 updates Actual Labor
User 2 updates Revenue Report
The current issue is that when User 2 (or any beyond User 1) tries to update the Revenue Report file, all of the links are using the addresses of the files on User 1's computer. Is there a way for the files to be set up or linked in a way that users don't constantly need to change the source to point to the copies on their local machines?
Doing research, I see G Suite allows for the use of the IMPORTRANGE function which allows the links to be static to a URL. I see that Microsoft is working on a similar feature. However, I'm wondering if there is a workaround in the meantime.
I haven't seen much regarding Collaborating in OneDrive beyond working on the same file simultaneously (as that's the primary goal of the feature). Is it powerful enough/set up to handle this as well? What files would need to be set to collaborate, and would there be a specific user in mind that would need to do this (i.e. the business owner "sharing" the file with managers)?