Excel file links through SharePoint

Question

I'm working for a small company which manages it's knowledge throughout Excel files. Some of the files contain data from other files, for example our orders file contains products that are saved in the product file (orders and products are separated files).

recently we decided to try share and connect some of the files using SharePoint office365 cloud system.

My problem is when i link between 2 files, excel doesn't reads the data from the source file until i open it. I don't want to open my source files anytime i want to update the other file. Especially when the data is stored on SharePoint cloud and every file save consumes Unnecessary DL bandwidth. Just to be clear:

• this problem also occur when i work on the files locally (not using SharePoint).
• the described problem is a simplification to my problem which contains many files and many connections, so i would really appreciate if you could supply a "wide solution"

My questions are:

• is there a way to update data from a closed sheet?

• are there any other options / tricks to manage relationships between tables?

• is there other solution for the problem? maybe using SharePoint built-in tables? maybe using other systems like google drive?

• other comments / thoughts? any idea would be nice

Answer 1

You could switch to an Access database and use that instead—it might be easier.